Converting Date to Time --- Please Help!

[PatrickMilano21]

I used the function: MRound(Cells(i, 1).Value, 1 / 24), to round each of my dates to the nearest hour. I now have cells that look like the following: (1/1/2014 12:00:00 AM), (1/1/2014 1:00:00 AM), (1/1/2014 2:00:00 AM)..... ETC.

How do I write a program that extracts JUST THE HOUR. SO for the examples above I would get:

12:00:00, 1:00:00, and 2:00:00.

Please help!!! Thank you!!

 

[SpillerBD]

=HOUR(DateTimeRef)

 

[deletedalien]

If the above was helpful you could (should) say so, and others searching for the same solution will know this worked, plus it gives the user who helped you positive feedback.

Just sayin'

 

[PatrickMilano21]

If the above was helpful you could (should) say so, and others searching for the same solution will know this worked, plus it gives the user who helped you positive feedback.

Just sayin'

shut your mouth....

 

[PatrickMilano21]

Hey SpillerBD, thank you so much for replying! I tried to use that before, but for some reason it was not helping

I ended up using: =TIME(HOUR(A1),MINUTE(A1),SECOND(A1)). I am going to try to use yours again though! Thank you so much for responding!

 

[SpillerBD]

=HOUR(DateTimeRef)

This function gives the numerical value of the hours as an integer from 0 to 23

However if you want the time you actually need:
=TIMEVALUE(TEXT(DateTimeRef,"HH:MM:SS"))
Make sure the cell is formatted to display as a time format such as "h:mm:ss"

(Yeah, it kept bothering me....)

 

[PatrickMilano21]

This function gives the numerical value of the hours as an integer from 0 to 23

However if you want the time you actually need:
=TIMEVALUE(TEXT(DateTimeRef,"HH:MM:SS"))
Make sure the cell is formatted to display as a time format such as "h:mm:ss"

(Yeah, it kept bothering me....)

I see, that makes perfect sense. Great, thanks again SpillerBD!

 

[RoryA]

shut your mouth....

I do not see any need for this kind of response. Please keep all communications civil in future!