Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to day and
First of all it is not 18:00PM. Miltary (24 hour time) does not use AM, PM.
If you want the hours between 7:00 and 18:00 to be day and the rest night, and A1 contains the date and time, the it's simply =IF(AND(MOD(A1,1)=VALUE("7:00"),MOD(A1,1)<=VALUE( "18:00")),"Day","Night") or =IF(AND(MOD(A1,1)=VALUE("7:00 AM"),MOD(A1,1)<=VALUE("6:00 PM")),"Day","Night") I find that easy to understand Tyro "kbee" wrote in message ... I need to convert the data from a cell that contains date and time to a different cell that will give a result of day /night, which functions should I use and what will be their syntax? if the hours i refere to as day are 7:00AM-18:00PM, and night 18:00Pm-07:00AM thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to day
the formula results: value and not d/n
how would u change it to express d/n with the original cell only includes the "hour am/pm" ? thanx "Tyro" wrote: First of all it is not 18:00PM. Miltary (24 hour time) does not use AM, PM. If you want the hours between 7:00 and 18:00 to be day and the rest night, and A1 contains the date and time, the it's simply =IF(AND(MOD(A1,1)=VALUE("7:00"),MOD(A1,1)<=VALUE( "18:00")),"Day","Night") or =IF(AND(MOD(A1,1)=VALUE("7:00 AM"),MOD(A1,1)<=VALUE("6:00 PM")),"Day","Night") I find that easy to understand Tyro "kbee" wrote in message ... I need to convert the data from a cell that contains date and time to a different cell that will give a result of day /night, which functions should I use and what will be their syntax? if the hours i refere to as day are 7:00AM-18:00PM, and night 18:00Pm-07:00AM thank you t |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count by date and hour | Excel Worksheet Functions | |||
Comparing specific date and hour with NOW() | Excel Worksheet Functions | |||
convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
Convert Date/Time to the Hour | Excel Discussion (Misc queries) | |||
Subtracting date:hour from date:hour | Excel Worksheet Functions |