what is the time of the day

[tomas]

I need a formula that will tell me the time of the day. i have a field with
the date and time (mm/dd/yy hh:mm PM). I need the formula to return the hour
of the day if the hour is after 5PM. For example, if it is 11/29/08 5:23 PM
i want it to return "5". If it is 11/27/08 6:46 PM, i want it to return "6".
For anything before 5PM, i want it to return "before 5".
Thanks.

 

[Jarek Kujawa]

try sth. like

=IF(HOUR(NOW())<5,"before 5",HOUR(NOW())))

adjust for AM/PM accordingly

 

[Peo Sjoblom]

=IF(MOD(NOW(),1)<--"17:00","Before 5",HOUR(NOW()))

you need to press F9 to update the formula

--


Regards,


Peo Sjoblom

 

[tomas]

If my date and time field is on column A, how do i edit your formula?

 

[tomas]

If my date and time field is on column A, how do i edit your formula?

 

[Fred Smith]

Your question was interpreted by both responders to mean you wanted to know
the time right now. If you want to check the hour of a time in a cell you
have, substitute that cell for "Now()", as in:

=if(hour(a1)<17,"Before 5",hour(a1)-12)

Regards,
Fred.

 

[tomas]

Beautiful! Thanks.

Your question was interpreted by both responders to mean you wanted to know
the time right now. If you want to check the hour of a time in a cell you
have, substitute that cell for "Now()", as in:

=if(hour(a1)<17,"Before 5",hour(a1)-12)

Regards,
Fred.