Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
The following code returns a value that looks like a date/time, but is not a
date/time. Eksample: The code returns a cell value 28.09.2007 13:58 (as seen in the formula field). Normally a date formated as "dd.mm.yyyy hh:mm" will return a value in the formula field like this: 28.09.2007 13:58:00, and it shows up as 28.09.2007 13:58 in the cell. I noticed that the code returns a value with one space between the date and the time. A properly formated date manually typed in has two spaces. When I manually change to two spaces between the time and the date in the value placed by the code it automatically changes from 28.09.2007 13:58 to 28.09.2007 13:58:00 in the formula field. The apperance in the cell is unchanged. The reason I discovered this was that I am checking for the largest value in two nearby cells =IF(L21K21;"Overdue";"") and it returned an unlogic responce. Whats wrong with this code? Private Sub CommandButton2_Click() .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" ActiveCell.Value = Format(Now(), "dd.mm.yyyy hh:mm") End Sub -- Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
if I format a cell (C2) with your formatting option and put =now() in it,
then in another cell use the =len(C2) I get 16 for which would indicate there is only one space between the date and time which confirms my previous experience. -- Regards, Tom Ogilvy "zzxxcc" wrote: The following code returns a value that looks like a date/time, but is not a date/time. Eksample: The code returns a cell value 28.09.2007 13:58 (as seen in the formula field). Normally a date formated as "dd.mm.yyyy hh:mm" will return a value in the formula field like this: 28.09.2007 13:58:00, and it shows up as 28.09.2007 13:58 in the cell. I noticed that the code returns a value with one space between the date and the time. A properly formated date manually typed in has two spaces. When I manually change to two spaces between the time and the date in the value placed by the code it automatically changes from 28.09.2007 13:58 to 28.09.2007 13:58:00 in the formula field. The apperance in the cell is unchanged. The reason I discovered this was that I am checking for the largest value in two nearby cells =IF(L21K21;"Overdue";"") and it returned an unlogic responce. Whats wrong with this code? Private Sub CommandButton2_Click() .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" ActiveCell.Value = Format(Now(), "dd.mm.yyyy hh:mm") End Sub -- Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
I also get 16 on both types of returned data. (The wrong and the correct).
That's because it is shown as 16 charters and spaces in the cell. Stll if you go into the formula bar on top of the Excel window you can count 20 charters and spaces. Forget about counting. Just lokk at the code. Something is wrong, but I can't see it. Here is a code that does a different task, but returns a correct date / time value: (the full code is IRRELEVANT, just a reference): Option Explicit Private Sub CommandButton1_Click() Dim NextCell As Range With Me Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) End With With NextCell .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" End With End Sub -- Thanks "Tom Ogilvy" wrote: if I format a cell (C2) with your formatting option and put =now() in it, then in another cell use the =len(C2) I get 16 for which would indicate there is only one space between the date and time which confirms my previous experience. -- Regards, Tom Ogilvy "zzxxcc" wrote: The following code returns a value that looks like a date/time, but is not a date/time. Eksample: The code returns a cell value 28.09.2007 13:58 (as seen in the formula field). Normally a date formated as "dd.mm.yyyy hh:mm" will return a value in the formula field like this: 28.09.2007 13:58:00, and it shows up as 28.09.2007 13:58 in the cell. I noticed that the code returns a value with one space between the date and the time. A properly formated date manually typed in has two spaces. When I manually change to two spaces between the time and the date in the value placed by the code it automatically changes from 28.09.2007 13:58 to 28.09.2007 13:58:00 in the formula field. The apperance in the cell is unchanged. The reason I discovered this was that I am checking for the largest value in two nearby cells =IF(L21K21;"Overdue";"") and it returned an unlogic responce. Whats wrong with this code? Private Sub CommandButton2_Click() .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" ActiveCell.Value = Format(Now(), "dd.mm.yyyy hh:mm") End Sub -- Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
I think the LEN() function returns the length of the value without any
formatting. The unformatted value for NOW() is a number like 39366.6878256944 Therefore LEN always returns 16 for a date value. The applied cell format has no influence. zzxxcc wrote: I also get 16 on both types of returned data. (The wrong and the correct). That's because it is shown as 16 charters and spaces in the cell. Stll if you go into the formula bar on top of the Excel window you can count 20 charters and spaces. Forget about counting. Just lokk at the code. Something is wrong, but I can't see it. Here is a code that does a different task, but returns a correct date / time value: (the full code is IRRELEVANT, just a reference): Option Explicit Private Sub CommandButton1_Click() Dim NextCell As Range With Me Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) End With With NextCell .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
Yes, you are correct that it does not seem to return the formatted length.
Nonetheless, the OP's problem seems to be that one of his functions is putting a text string in the cell and the other is putting a value in the cell. he does need to understand how dates/times are stored. http://www.cpearson.com/Excel/datetime.htm#SerialDates when assigning a date to a cell from vba it is best to assign the date serial to the cell and then format the cell as you want it to appear. -- Regards, Tom Ogilvy "Luca Brasi" wrote: I think the LEN() function returns the length of the value without any formatting. The unformatted value for NOW() is a number like 39366.6878256944 Therefore LEN always returns 16 for a date value. The applied cell format has no influence. zzxxcc wrote: I also get 16 on both types of returned data. (The wrong and the correct). That's because it is shown as 16 charters and spaces in the cell. Stll if you go into the formula bar on top of the Excel window you can count 20 charters and spaces. Forget about counting. Just lokk at the code. Something is wrong, but I can't see it. Here is a code that does a different task, but returns a correct date / time value: (the full code is IRRELEVANT, just a reference): Option Explicit Private Sub CommandButton1_Click() Dim NextCell As Range With Me Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) End With With NextCell .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
Thank you Tom and Luca,
Nice Date/Time page you refered to. Unfortunately I'm not yet an expert on VBA, but I have ordered John Walkenbach's book. I currently copy and paste codes I find on this excellent page. Can you propose a modification of my code? -- Thanks Tom Ogilvy skrev: Yes, you are correct that it does not seem to return the formatted length. Nonetheless, the OP's problem seems to be that one of his functions is putting a text string in the cell and the other is putting a value in the cell. he does need to understand how dates/times are stored. http://www.cpearson.com/Excel/datetime.htm#SerialDates when assigning a date to a cell from vba it is best to assign the date serial to the cell and then format the cell as you want it to appear. -- Regards, Tom Ogilvy "Luca Brasi" wrote: I think the LEN() function returns the length of the value without any formatting. The unformatted value for NOW() is a number like 39366.6878256944 Therefore LEN always returns 16 for a date value. The applied cell format has no influence. zzxxcc wrote: I also get 16 on both types of returned data. (The wrong and the correct). That's because it is shown as 16 charters and spaces in the cell. Stll if you go into the formula bar on top of the Excel window you can count 20 charters and spaces. Forget about counting. Just lokk at the code. Something is wrong, but I can't see it. Here is a code that does a different task, but returns a correct date / time value: (the full code is IRRELEVANT, just a reference): Option Explicit Private Sub CommandButton1_Click() Dim NextCell As Range With Me Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) End With With NextCell .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
Private Sub CommandButton2_Click() ' missing code here .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" ' End With ActiveCell.Value = Now ActiveCell.Numberformat = "dd.mm.yyyy hh:mm" End Sub so use the same method that works each time. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "zzxxcc" wrote: Thank you Tom and Luca, Nice Date/Time page you refered to. Unfortunately I'm not yet an expert on VBA, but I have ordered John Walkenbach's book. I currently copy and paste codes I find on this excellent page. Can you propose a modification of my code? -- Thanks Tom Ogilvy skrev: Yes, you are correct that it does not seem to return the formatted length. Nonetheless, the OP's problem seems to be that one of his functions is putting a text string in the cell and the other is putting a value in the cell. he does need to understand how dates/times are stored. http://www.cpearson.com/Excel/datetime.htm#SerialDates when assigning a date to a cell from vba it is best to assign the date serial to the cell and then format the cell as you want it to appear. -- Regards, Tom Ogilvy "Luca Brasi" wrote: I think the LEN() function returns the length of the value without any formatting. The unformatted value for NOW() is a number like 39366.6878256944 Therefore LEN always returns 16 for a date value. The applied cell format has no influence. zzxxcc wrote: I also get 16 on both types of returned data. (The wrong and the correct). That's because it is shown as 16 charters and spaces in the cell. Stll if you go into the formula bar on top of the Excel window you can count 20 charters and spaces. Forget about counting. Just lokk at the code. Something is wrong, but I can't see it. Here is a code that does a different task, but returns a correct date / time value: (the full code is IRRELEVANT, just a reference): Option Explicit Private Sub CommandButton1_Click() Dim NextCell As Range With Me Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) End With With NextCell .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
Thanks Tom,
I pasted: Private Sub CommandButton2_Click() ' missing code here .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" ' End With ActiveCell.Value = Now ActiveCell.Numberformat = "dd.mm.yyyy hh:mm" End Sub ....and I got this mesage: Compile error. Invalide or unqualifed reference. The ".Value = Now" was highlighted. Any suggestions? Should I add () after Now? -- Thanks Tom Ogilvy skrev: Private Sub CommandButton2_Click() ' missing code here .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" ' End With ActiveCell.Value = Now ActiveCell.Numberformat = "dd.mm.yyyy hh:mm" End Sub so use the same method that works each time. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "zzxxcc" wrote: Thank you Tom and Luca, Nice Date/Time page you refered to. Unfortunately I'm not yet an expert on VBA, but I have ordered John Walkenbach's book. I currently copy and paste codes I find on this excellent page. Can you propose a modification of my code? -- Thanks Tom Ogilvy skrev: Yes, you are correct that it does not seem to return the formatted length. Nonetheless, the OP's problem seems to be that one of his functions is putting a text string in the cell and the other is putting a value in the cell. he does need to understand how dates/times are stored. http://www.cpearson.com/Excel/datetime.htm#SerialDates when assigning a date to a cell from vba it is best to assign the date serial to the cell and then format the cell as you want it to appear. -- Regards, Tom Ogilvy "Luca Brasi" wrote: I think the LEN() function returns the length of the value without any formatting. The unformatted value for NOW() is a number like 39366.6878256944 Therefore LEN always returns 16 for a date value. The applied cell format has no influence. zzxxcc wrote: I also get 16 on both types of returned data. (The wrong and the correct). That's because it is shown as 16 charters and spaces in the cell. Stll if you go into the formula bar on top of the Excel window you can count 20 charters and spaces. Forget about counting. Just lokk at the code. Something is wrong, but I can't see it. Here is a code that does a different task, but returns a correct date / time value: (the full code is IRRELEVANT, just a reference): Option Explicit Private Sub CommandButton1_Click() Dim NextCell As Range With Me Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) End With With NextCell .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange? VBA code returns wrong date format (not a date)
Tom,
Sorry for confusing you. I pasted the wrong non-working code in my initial message / question. The code that didn't work was: Private Sub CommandButton2_Click() ActiveCell.Value = Format(Now(), "dd.mm.yyyy hh:mm") End Sub After getinng your reply I figured out that the correct code must be: Private Sub CommandButton2_Click() ' ActiveCell.Value = Format(Now(), "dd.mm.yyyy hh:mm") ActiveCell.Value = Now ActiveCell.NumberFormat = "dd.mm.yyyy hh:mm" End Sub -- Thanks "Tom Ogilvy" wrote: Private Sub CommandButton2_Click() ' missing code here .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" ' End With ActiveCell.Value = Now ActiveCell.Numberformat = "dd.mm.yyyy hh:mm" End Sub so use the same method that works each time. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "zzxxcc" wrote: Thank you Tom and Luca, Nice Date/Time page you refered to. Unfortunately I'm not yet an expert on VBA, but I have ordered John Walkenbach's book. I currently copy and paste codes I find on this excellent page. Can you propose a modification of my code? -- Thanks Tom Ogilvy skrev: Yes, you are correct that it does not seem to return the formatted length. Nonetheless, the OP's problem seems to be that one of his functions is putting a text string in the cell and the other is putting a value in the cell. he does need to understand how dates/times are stored. http://www.cpearson.com/Excel/datetime.htm#SerialDates when assigning a date to a cell from vba it is best to assign the date serial to the cell and then format the cell as you want it to appear. -- Regards, Tom Ogilvy "Luca Brasi" wrote: I think the LEN() function returns the length of the value without any formatting. The unformatted value for NOW() is a number like 39366.6878256944 Therefore LEN always returns 16 for a date value. The applied cell format has no influence. zzxxcc wrote: I also get 16 on both types of returned data. (The wrong and the correct). That's because it is shown as 16 charters and spaces in the cell. Stll if you go into the formula bar on top of the Excel window you can count 20 charters and spaces. Forget about counting. Just lokk at the code. Something is wrong, but I can't see it. Here is a code that does a different task, but returns a correct date / time value: (the full code is IRRELEVANT, just a reference): Option Explicit Private Sub CommandButton1_Click() Dim NextCell As Range With Me Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0) End With With NextCell .Value = Now .NumberFormat = "dd.mm.yyyy hh:mm" End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workday function returns the wrong date when the duration is 5 day | Excel Worksheet Functions | |||
date in wrong format | New Users to Excel | |||
Date in wrong format | Excel Discussion (Misc queries) | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
code pasting a date changes date format in current month only | Excel Programming |