Cell Value : Date & Time

[Ark68]

DIm n_date as date
Dim xst as date
Dim xet as date
Dim en as long
With worksheet("Test")                       
    st = 7/24
    et = 15/24
    n_date = 44033
    xst = n_date + st 'serial date + time
    xet = n_date + et 'serial date + time
    .Cells(2 9) = Format(xst, "h:mm am/pm")
    .Cells(2, 10) = Format(xet, "h:mm am/pm")
End With

With this code,
xst=44033.2916666666666667
xet=44033.625

Cell I2 displays 7:00 am. In the formula bar is displays as 7:00:00 AM
Cell J2 displays 3:00 pm. In the formula bar is displays as 3:00:00 PM

It appears as though these two cells have lost the date portion of the values xst and xet assigned to them. I'm not sure why the value changed, my intent was to keep the value (date + time) in the cell but format them only to reveal the time portion.

What must I do to retain the date portion as well as reference to the date portion will be needed at some point.



NXP

 

[Fluff]

You need to change the numberformat of the cell, not the actual value of the variable

With Worksheets("temp")
    st = 7 / 24
    et = 15 / 24
    n_date = 44033
    xst = n_date + st 'serial date + time
    xet = n_date + et 'serial date + time
    .Cells(2, 9) = xst
    .Cells(2, 10) = xet
    .Cells(2, 9).Resize(, 2).NumberFormat = "h:mm am/pm"
End With

 

[Ark68]

Thanks Fluff, so if I understand correctly (I've always struggled with the difference in use of format and numberformat), "format"ting will change the value of the cell, whereas NumberFormat keeps the value as assigned but sets the display?

As an aside ... what is the resize doing? My guess is it formats the cell next to it?

 

[Fluff]

NumberFormat keeps the value as assigned but sets the display

Correct
The format function will change the value of the variable, before it's put in the cell.
The resize does what's it's name suggests so it takes the range I9 & resizes it to 2 columns wide so you have I9:J9

 

[joeu2004]

To be clear, only the NumberFormat property changes the cell format in Excel.

The VBA Format function simply returns a string formatted according to the second parameter.

You "lost" the date information only because you did not include the date specifier in the Format string, to wit: "m/d/yyyy h:mm am/pm" or "m/d/yyyy h:m".

Without the date specifier, your original statements were effectively:

.Cells(2,9).Value = "7:00 AM"
.Cells(2,10).Value = "3:00 PM"

When we assign to the .Value property, Excel interprets the value just as if we had entered manually. So even though VBA passes a string, Excel automatically converts it to Excel time.

What Fluff did differently is: assign the entire numeric value (e.g. 44033.2916666666666667) to the cell. So the actual cell value is the date (7/21/2020) as well the time.

If the cell format is already some form of Date and/or Time format, Excel would display the full date and time.

But what Fluff also did is set the cell format to display only the time, even though cell value is still the date as well as time.

PS.... IMHO, it is better to set NumberFormat before assigning the value. Otherwise, if the cell format were Text initially, the assigned value will remain text -- that is, ISTEXT(I9)=TRUE -- even after we change the cell format with NumberFormat. (Until we "edit" the cell, for example by selecting the cell, putting the cursor in the Formula Bar, then pressing Enter.)

 

[Ark68]

Thanks folks for the lesson. Much appreciated.

 

[joeu2004]

Clarification....

IMHO, it is better to set NumberFormat before assigning the value. Otherwise, if the cell format were Text initially, the assigned value will remain text -- that is, ISTEXT(I9)=TRUE -- even after we change the cell format with NumberFormat. (Until we "edit" the cell, for example by selecting the cell, putting the cursor in the Formula Bar, then pressing Enter.)

I still hold that "IMHO". But the behavior depends on the type of your VBA variables (not shown). I had assumed that xst and xet are type Date. If they are type Variant or Double, the post-NumberFormat assignment would work as intended. But again, assigning NumberFormat is the prudent thing to do, since that always works, IIRC.

 

[joeu2004]

Critical typo, too late to edit....

I still hold that "IMHO". But the behavior depends on the type of your VBA variables (not shown). I had assumed that xst and xet are type Date. If they are type Variant or Double, the post-NumberFormat assignment would work as intended. But again, assigning NumberFormat is the prudent thing to do, since that always works, IIRC.

Well, duh! Aarrgghh! I meant to write: assigning NumberFormat first.