If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to day and
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
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to day and
With
A1: (a time value) Maybe this: B1: =IF(MOD(A1-TIME(7,,),24)TIME(11,,),"Day","Night") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "kbee" wrote in message news 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
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to day and
No conversion is necessary. Copy the cells and format the cells as time or
format the cells as time in place. For example, if your date/time cell is A1, then in B1 put =A1 and format B1 as time or simply format A1 as time. You can also format the cells as date/time. Tyro "kbee" wrote in message news 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
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to dayand
This isn't what the OP asked for - he/she wanted the words Day or
Night in one column dependent on the time in another column: Day is between 7:00 and 18:00, and Night is between 18:00 and 7:00 Ron's formula seems to do this (though I haven't tested it). Pete On Feb 13, 1:24*am, "Tyro" wrote: No conversion is necessary. Copy the cells and format the cells as time or format the cells as time in place. For example, if your date/time cell is A1, then in B1 put =A1 and format B1 as time or simply format A1 as time.. You can also format the cells as date/time. Tyro "kbee" wrote in message news 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- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
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 news 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 |
#6
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to day and
=IF(A1=TIME(18,,),"night","day")
"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 |
#7
|
|||
|
|||
iwhich funvtion to use : in conversion of date and hour to day and
It's interesting to note that with your formula, 12:01:00 AM returns Day.
Where I live in the USA, it is dark at that time. g Tyro "Teethless mama" wrote in message ... =IF(A1=TIME(18,,),"night","day") "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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|