| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Caluculate difference between two times
I am looking for a formula to calculate the difference between two times in
hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Caluculate difference between two times
assuming that neither of your start or end date/times will fall between 11 PM
Friday and 11 PM Sunday you can use this formula =(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-MOD(A1+"1:00",1))*24 note: NETWORKDAYS is part of Analysis ToolPak. If you get #NAME! error with the above then you need to install. Tools addins tick "Analysis ToolPak" "BRO" wrote: I am looking for a formula to calculate the difference between two times in hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Caluculate difference between two times
The formula work great for most of my data, however on ocassion I do have
start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop date however will never fall in this time period. Any ideas for these cases? -- BRO "daddylonglegs" wrote: assuming that neither of your start or end date/times will fall between 11 PM Friday and 11 PM Sunday you can use this formula =(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-MOD(A1+"1:00",1))*24 note: NETWORKDAYS is part of Analysis ToolPak. If you get #NAME! error with the above then you need to install. Tools addins tick "Analysis ToolPak" "BRO" wrote: I am looking for a formula to calculate the difference between two times in hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Caluculate difference between two times
Hi
Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis Toolpak, then =NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) If you want to exclude Holidays as well as weekends, then add the 3rd parameter to Networkdays which can either be cell references holding a list of holiday dates or a named range holding holiday dates =NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) where X1:X9 hold a list of holiday dates -- Regards Roger Govier "BRO" wrote in message ... I am looking for a formula to calculate the difference between two times in hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Caluculate difference between two times
The formula worked great. Any suggestions for when the start date would fall
between 11 PM Friday night and 11 PM Sunday? On ocassion the start date would fall in this time period but the stop date would not. On these ocassion the total time would start from 11 PM Sunday night. Thanks, -- BRO "Roger Govier" wrote: Hi Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis Toolpak, then =NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) If you want to exclude Holidays as well as weekends, then add the 3rd parameter to Networkdays which can either be cell references holding a list of holiday dates or a named range holding holiday dates =NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) where X1:X9 hold a list of holiday dates -- Regards Roger Govier "BRO" wrote in message ... I am looking for a formula to calculate the difference between two times in hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Caluculate difference between two times
Hi
I think the following will work, making use of a "helper cell". I used A2. In A2 =IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24=23), WEEKDAY(A1,2)5),DATE(YEAR(A1), MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1) Then in cell to have the result =IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24) -- Regards Roger Govier "BRO" wrote in message ... The formula worked great. Any suggestions for when the start date would fall between 11 PM Friday night and 11 PM Sunday? On ocassion the start date would fall in this time period but the stop date would not. On these ocassion the total time would start from 11 PM Sunday night. Thanks, -- BRO "Roger Govier" wrote: Hi Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis Toolpak, then =NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) If you want to exclude Holidays as well as weekends, then add the 3rd parameter to Networkdays which can either be cell references holding a list of holiday dates or a named range holding holiday dates =NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) where X1:X9 hold a list of holiday dates -- Regards Roger Govier "BRO" wrote in message ... I am looking for a formula to calculate the difference between two times in hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Caluculate difference between two times
Hello BRO
"however on ocassion I do have start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop date however will never fall in this time period" just amend my suggested formula to: =NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1) "Roger Govier" wrote: Hi I think the following will work, making use of a "helper cell". I used A2. In A2 =IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24=23), WEEKDAY(A1,2)5),DATE(YEAR(A1), MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1) Then in cell to have the result =IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24) -- Regards Roger Govier "BRO" wrote in message ... The formula worked great. Any suggestions for when the start date would fall between 11 PM Friday night and 11 PM Sunday? On ocassion the start date would fall in this time period but the stop date would not. On these ocassion the total time would start from 11 PM Sunday night. Thanks, -- BRO "Roger Govier" wrote: Hi Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis Toolpak, then =NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) If you want to exclude Holidays as well as weekends, then add the 3rd parameter to Networkdays which can either be cell references holding a list of holiday dates or a named range holding holiday dates =NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) where X1:X9 hold a list of holiday dates -- Regards Roger Govier "BRO" wrote in message ... I am looking for a formula to calculate the difference between two times in hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Caluculate difference between two times
Sorry, should be multiplied by 24 as per original...
=(NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1)) *24 "daddylonglegs" wrote: Hello BRO "however on ocassion I do have start times that fall between 11 PM on Friday and 11 PM on Sunday. The stop date however will never fall in this time period" just amend my suggested formula to: =NETWORKDAYS(A1+"1:00",B1+"1:00")-1+MOD(B1+"1:00",1)-NETWORKDAYS(A1+"1:00",A1+"1:00")*MOD(A1+"1:00",1) "Roger Govier" wrote: Hi I think the following will work, making use of a "helper cell". I used A2. In A2 =IF(OR(AND(WEEKDAY(A1,2)=5,MOD(A1,1)*24=23), WEEKDAY(A1,2)5),DATE(YEAR(A1), MONTH(A1),DAY(A1)+7-WEEKDAY(A1,2))+TIME(23,0,0),A1) Then in cell to have the result =IF(A1=A2,NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24),(B1-A2)*24) -- Regards Roger Govier "BRO" wrote in message ... The formula worked great. Any suggestions for when the start date would fall between 11 PM Friday night and 11 PM Sunday? On ocassion the start date would fall in this time period but the stop date would not. On these ocassion the total time would start from 11 PM Sunday night. Thanks, -- BRO "Roger Govier" wrote: Hi Assuming you have the Analysis Toolpak loaded, ToolsAddinsAnalysis Toolpak, then =NETWORKDAYS(A1,B1)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) If you want to exclude Holidays as well as weekends, then add the 3rd parameter to Networkdays which can either be cell references holding a list of holiday dates or a named range holding holiday dates =NETWORKDAYS(A1,B1,$X$1:$X$9)*24-MOD(A1,1)*24-(24-MOD(B1,1)*24) where X1:X9 hold a list of holiday dates -- Regards Roger Govier "BRO" wrote in message ... I am looking for a formula to calculate the difference between two times in hours. I also want to exclude the time between 11 PM on Friday night and 11 PM on Sunday night. Example Time 2: 1/23/07 8:31 (B1) Time 1: 1/19/07 13:52 (A1) Difference is 90.7 hrs Excluding weekend hrs difference would be 42.7 hrs -- BRO |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difference in two times over days | Excel Worksheet Functions | |||
| Calculate difference (mins) between 2 Times | Excel Worksheet Functions | |||
| HOURS DIFFERENCE IN TIME BETWEEN DIFFERENT DATES AND TIMES | Excel Worksheet Functions | |||
| Difference in Times | Excel Discussion (Misc queries) | |||
| Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) | |||
Hybrid Mode
