# Problem With Noon/Midnight

1. ## Problem With Noon/Midnight

So, in Cell F55, I have a start time and in J55 I have an end time.

F55 = 12:00:00 PM and J55 = 3:00:00 PM. I'm working on a macro that prints all the hours between the two in the immediate window, but I was getting something weird, it was printing from 1:00 AM until 3:00 PM, so I put "?timevalue(Range("F55").Value)" in the immediate window just to see what Excel thinks "12:00:00 PM" means and I got this:

``Please Login or Register  to view this content.``
Weird, huh? So to see what the value of "12:00 PM" as a double is, I got this:

``Please Login or Register  to view this content.``
And then, when I wanted to know what the time value of "0.5" is:

``Please Login or Register  to view this content.``
So, if I have 12:00 PM as the value of a cell, both of these come up with the same answer, and are both wrong:

``Please Login or Register  to view this content.``

To sum it up, Excel thinks 12:00 PM is 12:05 AM and it's killing me.

2. ## Re: Problem With Noon/Midnight

No mystery. A time is a number expressing a fractional part of a day. Hence 12:00 mid day, 12:00:00 PM, is half way through the day and is represented, as a number, by 0.5.

``Please Login or Register  to view this content.``

Regards, TMS

3. ## Re: Problem With Noon/Midnight

My problem is that timevalue(Range("F55").Value) comes out as 12:05:00 AM, so

``Please Login or Register  to view this content.``
prints as

``Please Login or Register  to view this content.``

``Please Login or Register  to view this content.``

4. ## Re: Problem With Noon/Midnight

Oh, nevermind, I just changed "Value" to "text" and it worked. Thanks!

5. ## Re: Problem With Noon/Midnight

Oh, OK ... did I not suggest that in post #2?

Regards, TMS

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

6. ## Re: Problem With Noon/Midnight

You did, I just misunderstood.

7. ## Re: Problem With Noon/Midnight

You're welcome. Thanks for the rep.

8. ## Re: Problem With Noon/Midnight

Looking at this again, TimeValue takes a text string that represents, or looks like, a time and converts it to a numeric value representing a fraction of a day. In this case, you actually have a true numeric time value in the cell ... at least, something that looks to me as something that Excel would interpret as a time.

Regards, TMS

9. ## Re: Problem With Noon/Midnight

Try converting the fraction value to a double before the conversion to time:
timevalue(Cdbl(Range("F55").Value))

There are currently 1 users browsing this thread. (0 members and 1 guests)