Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Variable
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Variable
The below Worksheet function will convert the number to date
=TEXT(39881,"dd-mm-yyyy") If this post helps click Yes --------------- Jacob Skaria "Ranjith Kurian" wrote: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Variable
Thanks for the reply,but Actually i need my variables(curmth and prvmth) to
change to numbers not the active cell. "Jacob Skaria" wrote: The below Worksheet function will convert the number to date =TEXT(39881,"dd-mm-yyyy") If this post helps click Yes --------------- Jacob Skaria "Ranjith Kurian" wrote: 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Variable
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" wrote in message ... 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Variable
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? -- Rick (MVP - Excel) "Ranjith Kurian" wrote in message ... 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Variable
thanks a lot
"Tim Zych" wrote: 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" wrote in message ... 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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Variable
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 "Tim Zych" wrote: 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" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Number to variable date | Excel Programming | |||
variable for Date | Excel Programming | |||
add 1 day to date variable | Excel Programming | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions | |||
Compare Date in Cell VS a Variable Date to Hide Row | Excel Programming |