|
iwhich funvtion to use : in conversion of date and hour to day and
Did you mean MOD(...,1), rather than MOD(...,24), Ron?
-- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day and
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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day
sorry, but how do I change it if i decided to use just the hour AM/PM
without the m/d/y? thnx again "Ron Coderre" wrote: 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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day
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) "kbee" wrote in message ... Thank you, it worked , i would appreciate if you explained what each value/name stands for, if u could.thanx. best regards, kbee "Ron Coderre" wrote: 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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day
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) "Ron Coderre" wrote in message ... 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) "kbee" wrote in message ... Thank you, it worked , i would appreciate if you explained what each value/name stands for, if u could.thanx. best regards, kbee "Ron Coderre" wrote: 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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day
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 "Ron Coderre" wrote: 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) "Ron Coderre" wrote in message ... 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.) = -0. MOD(-0., 1) returns 0. which is 8PM. I hope that helps. ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "kbee" wrote in message ... Thank you, it worked , i would appreciate if you explained what each value/name stands for, if u could.thanx. best regards, kbee "Ron Coderre" wrote: 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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day
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 a B1 returns Day B2 returns Night Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "kbee" wrote in message ... 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 "Ron Coderre" wrote: 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) "Ron Coderre" wrote in message ... 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.) = -0. MOD(-0., 1) returns 0. which is 8PM. I hope that helps. ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "kbee" wrote in message ... Thank you, it worked , i would appreciate if you explained what each value/name stands for, if u could.thanx. best regards, kbee "Ron Coderre" wrote: 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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day
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 "kbee" wrote: 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 "Ron Coderre" wrote: 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) "Ron Coderre" wrote in message ... 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.) = -0. MOD(-0., 1) returns 0. which is 8PM. I hope that helps. ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "kbee" wrote in message ... Thank you, it worked , i would appreciate if you explained what each value/name stands for, if u could.thanx. best regards, kbee "Ron Coderre" wrote: 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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
iwhich funvtion to use : in conversion of date and hour to day
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) "kbee" wrote in message ... 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 "kbee" wrote: 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 "Ron Coderre" wrote: 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) "Ron Coderre" wrote in message ... 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.) = -0. MOD(-0., 1) returns 0. which is 8PM. I hope that helps. ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "kbee" wrote in message ... Thank you, it worked , i would appreciate if you explained what each value/name stands for, if u could.thanx. best regards, kbee "Ron Coderre" wrote: 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) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you mean MOD(...,1), rather than MOD(...,24), Ron? -- David Biddulph "Ron Coderre" wrote in message ... 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 ... 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 |
| All times are GMT +1. The time now is 10:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com