Strange? VBA code returns wrong date format (not a date)

[Guest]

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(L21>K21;"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

 

[Guest]

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.

 

[Guest]

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

 

[Luca Brasi]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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