Date Variable

[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)

 

[Jacob Skaria]

The below Worksheet function will convert the number to date

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

If this post helps click Yes

 

[Ranjith Kurian]

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

 

[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

 

[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?

 

[Ranjith Kurian]

thanks a lot

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

 

[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