| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
what is the time of the day
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
what is the time of the day
try sth. like
=IF(HOUR(NOW())<5,"before 5",HOUR(NOW()))) adjust for AM/PM accordingly On 11 Gru, 16:25, tomas wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
what is the time of the day
=IF(MOD(NOW(),1)<--"17:00","Before 5",HOUR(NOW()))
you need to press F9 to update the formula -- Regards, Peo Sjoblom "tomas" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
what is the time of the day
If my date and time field is on column A, how do i edit your formula?
"Peo Sjoblom" wrote: =IF(MOD(NOW(),1)<--"17:00","Before 5",HOUR(NOW())) you need to press F9 to update the formula -- Regards, Peo Sjoblom "tomas" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
what is the time of the day
If my date and time field is on column A, how do i edit your formula?
"Jarek Kujawa" wrote: try sth. like =IF(HOUR(NOW())<5,"before 5",HOUR(NOW()))) adjust for AM/PM accordingly On 11 Gru, 16:25, tomas wrote: 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
what is the time of the day
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" wrote in message ... If my date and time field is on column A, how do i edit your formula? "Peo Sjoblom" wrote: =IF(MOD(NOW(),1)<--"17:00","Before 5",HOUR(NOW())) you need to press F9 to update the formula -- Regards, Peo Sjoblom "tomas" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
what is the time of the day
Beautiful! Thanks.
"Fred Smith" wrote: 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" wrote in message ... If my date and time field is on column A, how do i edit your formula? "Peo Sjoblom" wrote: =IF(MOD(NOW(),1)<--"17:00","Before 5",HOUR(NOW())) you need to press F9 to update the formula -- Regards, Peo Sjoblom "tomas" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| straight time, time and a half, and double time | Excel Discussion (Misc queries) | |||
| Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
| verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
| Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Linear Mode
