Reading Time from cells

[JTBorton]

I am recently posted a thread about some trouble I was having formatting cells on my spreadsheet to display time. The user enters the start time of a meeting on a form. The hour and minutes are placed in a text box in the format 11:00. Then they select radio buttons for AM/PM. The problem arose when I tried to put this information into the spreadsheet. Instead of displaying the time in h:mm AM/PM format it came out as a big, ugly, nasty decimal number. This problem was solved, with a little help from members of this forum, as follows:

.Cells(3, 4) = Trim(txtTime.Text) & IIf(optAM, " AM", " PM")
.Cells(3, 4).NumberFormat = "h:mm AM/PM"

Now the problem I have is reading the cells and converting the time back to the user form for editing. It will not read the time as a text string which I can then manipulate, but instead the big, hairy, nasty 0.458333333333333. It even cuts off my AM/PM when I read it in. How can I read the time into a variable in a way that I can extract the information that I want?

 

[macropod]

Hi JTBorton,

Hint:

Sub Test()
Dim Tm As Single
Tm = 0.458333333333333
MsgBox Format(Tm, "HH:mm AM/PM")
End Sub

Cheers

[MS MVP - Word]

 

[Golom]

See if you can make use of

? format(0.458333333333333,"h:nn AM/PM")
11:00 AM

 

[SkipVought]

Please read and understand...

faq68-5827.

The value that you posted can be FORMATTED (Not changed) as 11:00:00 AM. The VALUE is still 0.458333333333333.

YourRange.NumberFormat = "h:mm:ss AM/PM"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

You may also consider the following:
Cells(3, 4).Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

All these answers worked great. Thanks alot