Retrieving time value from cell as decimal. Why?

[What-A-Tool]

I place a time value in a cell - this time value is user selected from a
DateTime picker control on a form.
When I retrieve this time from the cell, it comes back as a decimal value.
It's driving me nuts - I can't see what I'm doing stupid here - can someone
please shed some light on this ?


dtpDayStart - Format: dtpCustom CustomFormat: h:mm tt
Cell A3 - Format: Time Type: 13:30


Save to cell : ----------------------------------------------

Dim vntStrt As Variant

vntStrt = Split(Format(dtpDayStart.Value, "hh:mm"), ":")

Worksheets("UserData").Range("A3").Value = _
vntStrt(0) & ":" & RoundToQuarterHour(vntStrt(1))

<<vntStrt(0) & ":" & RoundToQuarterHour(vntStrt(1))>> "This is
saving a value of 07:00 to cell A3"
The value as viewed in the Excel InsertFunction bar
is 7:00:00 AM, viewed in the cell as 7:00

Read from cell : ---------------------------------------------

Dim dtmDayStrt As Date

dtmDayStrt = Worksheets("UserData").Range("A3").Value


<<dtmDayStrt = Worksheets("UserData").Range("A3").Value>> "This
is returning a value of 0.291666666666667"
This same value is retrieved even if I save
dtpDayStart.Value directly to the cell, so its not a result of the way I'm
concatenating it.


The strange thing is, I am saving a time value into cell B3 as well, using
the exact same code to read and write as above for A3, and yet it reads this
as a time, just as I expect. @#$%%^!!
I have manually formatted A3 as time, then I have used the formatpainter to
make A3 the same as B3, all with the same result.


Thanks for any help -
Sean

 

[Guest]

Hi Sean -
Excel displays timevalues just about any way you desire, but it always
stores an underlying value as the portion of a 24-hour period that the time
represents. In your case, 7am = 29.166667% of the day (7 hours since
midnight DIVIDED BY 24).

So, if you are storing the timevalue in a cell, reformat (or even
pre-format) the cell with whatever format you want. If you are storing the
data in a TextBox on a UserForm, you could programmatically insert it in the
box and format it at the same time as:

UserForm1.TextBox1.value=format(dtmDayStrt,"hh:mm")

 

[What-A-Tool]

Thanks Jay -

I had tried formatting it as a time, buit because of the way I was doing
things, I kept throwing an error message in certain instances. After reading
your reply, I put in an an error handler, formatted my decimal as a time,
and re-arranged my code a bit, and everything is working fine now.
Still seems kinda strange that one cell is returning a time, and the other a
decimal, but I can live with it now.

Thanks again - Sean