| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
which function to use: in conversion of date and hour to day/night
why do u use mod?
cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM cell b1 or b2= day/night what should be the syntax? thanks to anyone who can help with this. "kbee" wrote: 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
|
|||
|
|||
|
which function to use: in conversion of date and hour to day/night
Dates in Excel are maintained as whole numbers. Jan 1, 1900 is day 1, Feb
12, 2008 is day 39,340 and Excel's highest date, Dec 31, 9999 is day 2,948,465. Times in Excel are maintained as decimal fractions of 24 hours. So, 12:00AM, midnight, is 0.000000, 12:01 AM, 1 minute after midnight is 0.000694. (1/(24*60) 1am is 1/24 = 0.041667. 12PM noon is 12/25 = .5 so noon on Feb 12, 2008 is: 39340.5 If there is no date, the time is simply 0.5 Using =MOD(39340.5,1) produces the remainder after dividing by 1, i.e. 0.5, the time portion of the date/time. Most books on Excel explain this. Tyro "kbee" wrote in message ... why do u use mod? cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM cell b1 or b2= day/night what should be the syntax? thanks to anyone who can help with this. "kbee" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
which function to use: in conversion of date and hour to day/night
Correction
12/24 is noon 0.5 Tyro "Tyro" wrote in message ... Dates in Excel are maintained as whole numbers. Jan 1, 1900 is day 1, Feb 12, 2008 is day 39,340 and Excel's highest date, Dec 31, 9999 is day 2,948,465. Times in Excel are maintained as decimal fractions of 24 hours. So, 12:00AM, midnight, is 0.000000, 12:01 AM, 1 minute after midnight is 0.000694. (1/(24*60) 1am is 1/24 = 0.041667. 12PM noon is 12/25 = .5 so noon on Feb 12, 2008 is: 39340.5 If there is no date, the time is simply 0.5 Using =MOD(39340.5,1) produces the remainder after dividing by 1, i.e. 0.5, the time portion of the date/time. Most books on Excel explain this. Tyro "kbee" wrote in message ... why do u use mod? cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM cell b1 or b2= day/night what should be the syntax? thanks to anyone who can help with this. "kbee" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
which function to use: in conversion of date and hour to day/night
I already gave you the formula. If you choose not to use it, ok!
Tyro "kbee" wrote in message ... why do u use mod? cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM cell b1 or b2= day/night what should be the syntax? thanks to anyone who can help with this. "kbee" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
which function to use: in conversion of date and hour to day/n
Thank you for your atempt but the suggestion did not yield the result, and i
dont know how to correct it. thanx again "Tyro" wrote: I already gave you the formula. If you choose not to use it, ok! Tyro "kbee" wrote in message ... why do u use mod? cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM cell b1 or b2= day/night what should be the syntax? thanks to anyone who can help with this. "kbee" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| iwhich funvtion to use : in conversion of date and hour to day and | Excel Worksheet Functions | |||
| iwhich funvtion to use : in conversion of date and hour to day and | Excel Worksheet Functions | |||
| Count by date and hour | Excel Worksheet Functions | |||
| Hour() function | Excel Worksheet Functions | |||
| Subtracting date:hour from date:hour | Excel Worksheet Functions | |||
Linear Mode
