 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008iwhich 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 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) >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 [quoted text clipped - 3 lines] > 18:00Pm-07:00AM > thank you Did you mean MOD(...,1), rather than MOD(...,24), Ron?  SignatureDavid Biddulph > With > A1: (a time value) [quoted text clipped - 18 lines] >> 18:00Pm-07:00AM >> thank you Hmmmm....Yes, I didn't notice the typo. Yet, all of my tests returned correct values. I probably missed a test instance where the 24 wouldn't have worked. Yup. It can fail if the date is yesterday. Corrected formula: B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night") Thanks! -------------------------- Best Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Did you mean MOD(...,1), rather than MOD(...,24), Ron? >> With [quoted text clipped - 19 lines] >>> 18:00Pm-07:00AM >>> thank you Thank you, it worked , i would appreciate if you explained what each value/name stands for, if u could.thanx. best regards, kbee L4Kbea@hotmail.com > Hmmmm....Yes, I didn't notice the typo. > Yet, all of my tests returned correct values. [quoted text clipped - 40 lines] > >>> 18:00Pm-07:00AM > >>> thank you Question_1: How does the formula work Answer: Regarding: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night") According to the rules: The DAY category includes times that are >=7AM and <6PM The NIGHT category includes times that are >=6PM and <7AM That puts the DAY category in the middle range of times: Actual Time: 00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23 N__N__N__N__N__N__D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N__N__N and complicates the formula by having it check if the value is "between" 2 times. By subtracting 7 hours from the time, we only need to test if the time is less than 11AM (for DAY) Actual Time: 07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23_24_01_02_03_04_05_06 Adjusted Time: 00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23 D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N__N__N__N__N__N__N__N__N To Excel.... DATES are the number of days since 31-DEC-1899. 1 = 01-JAN-1900 39,492 = 14-FEB-2008 TIMES are decimal fractions of a day Noon = 0.5 (12hrs/24hrs) Noon on 14-FEB-2008 is: 39,492.5 Since we are only testing time, and not date, we use the MOD function to remove the integer part of the date/time....leaving only the time. So....the formula: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night") . Starts with the actual date/time: A1 . Subtracts 7 hours from that value: A1-TIME(7,,) . Removes the date component: MOD(A1-TIME(7,,),1)..AND..fix negative values. . Tests if that adjusted time is less than 11AM . If YES...Day, otherwise...Night. Question_2: If I just use Times, with without dates, how does the formula change. Answer: It doesn't. Here's why: If the Time is 3AM, subtracting 7 hours returns a negative number, which cannot be a time....so we still need to MOD function to fix that issue: =(3AM-7AM) = (3/24-7/24) = (0.125 - 0.291666666666667) = -0.166666666666667 MOD(-0.166666666666667, 1) returns 0.833333333333333 which is 8PM. I hope that helps. ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Thank you, it worked , i would appreciate if you explained what each > value/name stands for, if u could.thanx. [quoted text clipped - 47 lines] >> >>> 18:00Pm-07:00AM >> >>> thank you A couple typos: Correction_1: Actual Time: 00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23 N__N__N__N__N__N__N__D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N__N (6AM should be "N"...and wasn't) ~~~~~~~~~~~~~~~~~~~~~~~~~~ Correction_2: Adjusted Time: 00_01_02_03_04_05_06_07_08_09_10_11_12_13_14_15_16_17_18_19_20_21_22_23 D__D__D__D__D__D__D__D__D__D__D__N__N__N__N__N__N__N__N__N__N__N__N__N (10AM should be "D"...and wasn't) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Question_1: How does the formula work > [quoted text clipped - 122 lines] >>> >>> 18:00Pm-07:00AM >>> >>> thank you Hi Ron, your explanation was great, i tried it but i am novice at thiis and i keep messing up on the hour , if cell a1=13:00 Pm and cella2= 18:00Pm , i want it to show in cell b1=day and cell b2=night. i put the mod with the a1-7/24 but it did not result correctly, i must do something wrong, my last request for the syntax for it. thanks, bee > A couple typos: > [quoted text clipped - 152 lines] > >>> >>> 18:00Pm-07:00AM > >>> >>> thank you First: As David Biddulph astutely pointed out.... there was a flaw in my original formula. This formula: =IF(MOD(A1-TIME(7,,),24)<TIME(11,,),"Day","Night") Should be: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night") Notice that the number 24 has been replaced with the number 1. But, that wouldn't impact your latest scenario: Try this: A1: 1:00:00 PM A2: 6:00:00 PM B1: =IF(MOD(A1-TIME(7,,),1)<TIME(11,,),"Day","Night") Copy B1 into B2 The results are: B1 returns Day B2 returns Night Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Hi Ron, > your explanation was great, i tried it but i am novice at thiis and i keep [quoted text clipped - 166 lines] >> >>> >>> 18:00Pm-07:00AM >> >>> >>> thank you Thiis is really strange, I tried your formula on my old table, yesterday and it worked, today i built a new one however it gave the result of a value and yet in a different table the cell just contains the formula??? how is it possible, i check the formatings and they are identical??? what could cause the problem??? bee again > Hi Ron, > your explanation was great, i tried it but i am novice at thiis and i keep [quoted text clipped - 161 lines] > > >>> >>> 18:00Pm-07:00AM > > >>> >>> thank you Well, there could be a few reasons. First, from the Main Menu: <tools><options><view tab>....UNcheck: Formulas. Second, set the number format of the cell to General Third, select the cell, press [F2], then press [ENTER] Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Thiis is really strange, I tried your formula on my old table, yesterday > and [quoted text clipped - 178 lines] >> > >>> >>> 18:00Pm-07:00AM >> > >>> >>> thank you sorry, but how do I change it if i decided to use just the hour AM/PM without the m/d/y? thnx again > Hmmmm....Yes, I didn't notice the typo. > Yet, all of my tests returned correct values. [quoted text clipped - 40 lines] > >>> 18:00Pm-07:00AM > >>> thank you 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 >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 [quoted text clipped - 3 lines] > 18:00Pm-07:00AM > thank you 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 > 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 [quoted text clipped - 12 lines] > > - Show quoted text - 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 >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 [quoted text clipped - 3 lines] > 18:00Pm-07:00AM > thank you 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 > 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, [quoted text clipped - 20 lines] > > t =IF(A1>=TIME(18,,),"night","day") > 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 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 > =IF(A1>=TIME(18,,),"night","day") > [quoted text clipped - 5 lines] >> 18:00Pm-07:00AM >> thank you 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. > 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 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 > why do u use mod? > cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM [quoted text clipped - 9 lines] >> 18:00Pm-07:00AM >> thank you Correction 12/24 is noon 0.5 Tyro > 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 [quoted text clipped - 22 lines] >>> 18:00Pm-07:00AM >>> thank you I already gave you the formula. If you choose not to use it, ok! Tyro > why do u use mod? > cell a1=1/1/08 08:00:00 AM, cell a2= 1/1/08 22:00:00 PM [quoted text clipped - 9 lines] >> 18:00Pm-07:00AM >> thank you Thank you for your atempt but the suggestion did not yield the result, and i dont know how to correct it. thanx again > I already gave you the formula. If you choose not to use it, ok! > [quoted text clipped - 13 lines] > >> 18:00Pm-07:00AM > >> thank you |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.