Date Variable

R

Ranjith Kurian

I have created the below variable to get current month and previous month

curmth = Format(Date, "mmm-yy")
prvmth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yy")

and i have made my active cell to value2
dt = Cells(i, 4).Value2

So the active cell convert the date to values/numbers, i need to compare my
above variable with the activecell, as the format are different i am
unsucceful in this.

Could someone advise me how to change my variables(curmth and prvmth) to
values/number (example: 3/9/2009is the date and the number for that is 39881)
 
J

Jacob Skaria

The below Worksheet function will convert the number to date

=TEXT(39881,"dd-mm-yyyy")

If this post helps click Yes
 
R

Ranjith Kurian

Thanks for the reply,but Actually i need my variables(curmth and prvmth) to
change to numbers not the active cell.
 
T

Tim Zych

Dim n1 As Double, n2 As Double
' Convert a string which appears to be a date to a double
n1 = CDbl(CDate("1/1/2009 12:00 AM"))
' Convert a date value to a double. CDate is used here for consistency.
n2 = CDbl(CDate(#1/1/2009#))

' Since this is already a date, CDate is not needed, so this could be
alternately:
'n2 = CDbl(#1/1/2009#)

Debug.Print n1 & vbLf & n2
Debug.Print n1 = n2

' Or your example:

Dim curmth As String, prvmth As String, dt As Date

curmth = Format(Date, "mmm-yy")
prvmth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yy")

n1 = CDbl(CDate(curmth))

ActiveCell.Value = curmth
dt = CDate(ActiveCell.Value)

n2 = CDbl(CDate(dt))

Debug.Print n1 & vbLf & n2
Debug.Print n1 = n2
 
R

Rick Rothstein

I confused as to what you are attempting to do. You call curmth and prvmth
the "current month" and "previous month", but what you are assigning to
those variables are not months, but rather text strings showing an
abbreviated month name followed by a dash followed by the 2-digit year (for
example, today would be Mar-09). Then you seem to be saying you want to
compare these text strings to the serialized date number (for example, 39897
for today's date) in the "active cell" (which is not the ActiveCell, but
rather is a cell from a loop). Also, you don't tell us what you mean by
"compare"... what kind of comparison are you trying to do (are you looking
for them to be equal, in a range, something else)? So that is why I am
confused as to what you are attempting to do. I think the answer to your
question lies in what you actually mean by "compare"... can you describe it
in more detail for us?
 
R

Ranjith Kurian

thanks a lot

Tim Zych said:
Dim n1 As Double, n2 As Double
' Convert a string which appears to be a date to a double
n1 = CDbl(CDate("1/1/2009 12:00 AM"))
' Convert a date value to a double. CDate is used here for consistency.
n2 = CDbl(CDate(#1/1/2009#))

' Since this is already a date, CDate is not needed, so this could be
alternately:
'n2 = CDbl(#1/1/2009#)

Debug.Print n1 & vbLf & n2
Debug.Print n1 = n2

' Or your example:

Dim curmth As String, prvmth As String, dt As Date

curmth = Format(Date, "mmm-yy")
prvmth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yy")

n1 = CDbl(CDate(curmth))

ActiveCell.Value = curmth
dt = CDate(ActiveCell.Value)

n2 = CDbl(CDate(dt))

Debug.Print n1 & vbLf & n2
Debug.Print n1 = n2



--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions
 
R

Ranjith Kurian

Hi

I have one problem with the below code, it showing me the march 9th date,
how will i change it to 1st of every month, so that next month when i run
this it should show me 1st of April.

curmt = Format(Date, "mmm-yy")
curmth = CDbl(CDate(curmt))
MsgBox curmth

Answer is 39881, the required answer is 39873
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Variable 1
Select Case 8
variable for Date 10
Convert Date 3
How to use/create code in MS Word to calculate age? 0
Date Format - simple question 1
Countdown macro - display settings 2
Date format. 2

Top