Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
I am struggling to find a formula to calculate exact time between two date/time values excluding out-of-office times. Let me explain: I have two dates, the date/time a task was assigned to a department to be worked and the date/time the work was completed by the department. This department has set office hours during the Mon-Fri workweek and then different office hours on Sat and they do not work on Sun. I am trying to create a formula where the time they are not in the office does not count against them for a true completion time. I would like the formula to be dynamic so that if the office hours change I can simply put in new office hours at cells on a different worksheet. Start date/time = 5/2/2006 16:15 Completed date/time = 5/8/2006 15:59 Simple subtraction = 5.988888889 Manual calculation = 2.447222222 Mon-Fri Office hours = 7:00am - 7:00pm Sat Office hours = 7:00am - 6:00pm They way I did the manaul calculation is like this: 5/2/2006 16:15 5/8/2006 15:59 5.988888889 Total Time 5/2/2006 19:00 5/3/2006 7:00 0.5 Out of Office 5/3/2006 18:00 5/5/2006 7:00 1.541666667 Out of Office 5/5/2006 19:00 5/6/2006 7:00 0.5 Out of Office 5/6/2006 19:00 5/7/2006 7:00 0.5 Out of Office 5/7/2006 19:00 5/8/2006 7:00 0.5 Out of Office 2.447222222 Subtract Total Time from Out of Office times. Here is some sample data from my report of thousands of tickets compelted by this department: Created Date Closed Date 5/2/2006 16:15 5/8/2006 15:59 5/3/2006 9:45 5/10/2006 8:10 5/3/2006 16:00 5/5/2006 16:34 5/4/2006 8:47 5/8/2006 15:25 5/4/2006 15:30 5/5/2006 16:33 5/4/2006 16:00 5/5/2006 16:41 5/5/2006 17:45 5/8/2006 9:33 5/6/2006 14:45 5/8/2006 9:16 5/6/2006 15:00 5/8/2006 9:24 5/6/2006 15:15 5/8/2006 9:51 5/6/2006 15:45 5/8/2006 9:39 5/6/2006 15:45 5/8/2006 10:19 5/6/2006 16:00 5/8/2006 9:45 5/6/2006 16:00 5/8/2006 9:46 5/7/2006 10:58 5/8/2006 15:22 5/7/2006 10:58 5/8/2006 16:57 5/9/2006 13:45 5/17/2006 15:08 5/10/2006 10:59 5/11/2006 11:18 5/11/2006 13:48 5/12/2006 17:08 5/12/2006 11:15 5/13/2006 13:38 5/12/2006 11:15 5/15/2006 10:10 5/12/2006 11:15 5/15/2006 10:20 5/12/2006 11:31 5/13/2006 13:50 5/12/2006 11:31 5/13/2006 14:10 Has someone done this before, or is smart enough to create a formula that will do it for me???? Wesley -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
Some examples here
http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "wahiggin" wrote in message ... I am struggling to find a formula to calculate exact time between two date/time values excluding out-of-office times. Let me explain: I have two dates, the date/time a task was assigned to a department to be worked and the date/time the work was completed by the department. This department has set office hours during the Mon-Fri workweek and then different office hours on Sat and they do not work on Sun. I am trying to create a formula where the time they are not in the office does not count against them for a true completion time. I would like the formula to be dynamic so that if the office hours change I can simply put in new office hours at cells on a different worksheet. Start date/time = 5/2/2006 16:15 Completed date/time = 5/8/2006 15:59 Simple subtraction = 5.988888889 Manual calculation = 2.447222222 Mon-Fri Office hours = 7:00am - 7:00pm Sat Office hours = 7:00am - 6:00pm They way I did the manaul calculation is like this: 5/2/2006 16:15 5/8/2006 15:59 5.988888889 Total Time 5/2/2006 19:00 5/3/2006 7:00 0.5 Out of Office 5/3/2006 18:00 5/5/2006 7:00 1.541666667 Out of Office 5/5/2006 19:00 5/6/2006 7:00 0.5 Out of Office 5/6/2006 19:00 5/7/2006 7:00 0.5 Out of Office 5/7/2006 19:00 5/8/2006 7:00 0.5 Out of Office 2.447222222 Subtract Total Time from Out of Office times. Here is some sample data from my report of thousands of tickets compelted by this department: Created Date Closed Date 5/2/2006 16:15 5/8/2006 15:59 5/3/2006 9:45 5/10/2006 8:10 5/3/2006 16:00 5/5/2006 16:34 5/4/2006 8:47 5/8/2006 15:25 5/4/2006 15:30 5/5/2006 16:33 5/4/2006 16:00 5/5/2006 16:41 5/5/2006 17:45 5/8/2006 9:33 5/6/2006 14:45 5/8/2006 9:16 5/6/2006 15:00 5/8/2006 9:24 5/6/2006 15:15 5/8/2006 9:51 5/6/2006 15:45 5/8/2006 9:39 5/6/2006 15:45 5/8/2006 10:19 5/6/2006 16:00 5/8/2006 9:45 5/6/2006 16:00 5/8/2006 9:46 5/7/2006 10:58 5/8/2006 15:22 5/7/2006 10:58 5/8/2006 16:57 5/9/2006 13:45 5/17/2006 15:08 5/10/2006 10:59 5/11/2006 11:18 5/11/2006 13:48 5/12/2006 17:08 5/12/2006 11:15 5/13/2006 13:38 5/12/2006 11:15 5/15/2006 10:10 5/12/2006 11:15 5/15/2006 10:20 5/12/2006 11:31 5/13/2006 13:50 5/12/2006 11:31 5/13/2006 14:10 Has someone done this before, or is smart enough to create a formula that will do it for me???? Wesley -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
Is there a way to change the NETWORKDAYS function to accommodate a 6 day work week? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
I am trying to combined a couple of formulas from posts that I received and keep getting an error message. NETWORKDAYS doesn't work by itself because I have one extra work day, Saturday. So I was trying to combine this: =NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7) and this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) A1 = Start Time B1 = End Time A2 = Task Start Date/Time B2 = Task End Date/Time D range = Holidays (my exmaple only had three holidays so it was D1:D3) To make this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) But I keep getting an error message. Any ideas? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
You are limited to 255 characters in a formula - you have 365 in your
final formula, so you'll have to approach it differently. Can you split your formula and make use of helper cells? It strikes me that a UDF might be a better approach here. Hope this helps. Pete wahiggin wrote: I am trying to combined a couple of formulas from posts that I received and keep getting an error message. NETWORKDAYS doesn't work by itself because I have one extra work day, Saturday. So I was trying to combine this: =NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7) and this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) A1 = Start Time B1 = End Time A2 = Task Start Date/Time B2 = Task End Date/Time D range = Holidays (my exmaple only had three holidays so it was D1:D3) To make this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) But I keep getting an error message. Any ideas? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
Whats a udf? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
UDF stands for a User Defined Function. This is one which you write
yourself in VBA and can use from your own worksheet as if it were one of Excel's functions. Let's say you had a UDF called "Calc_time(cell_ref1, cell_ref2 )", then you would enter this formula in a cell: =Calc_time(A1,B1) and it would return the result of whatever it calculates. Hope this explains. Pete wahiggin wrote: Whats a udf? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
The limit is 1024 characters when measured in R1C1 notation--not 255.
Pete_UK wrote: You are limited to 255 characters in a formula - you have 365 in your final formula, so you'll have to approach it differently. Can you split your formula and make use of helper cells? It strikes me that a UDF might be a better approach here. Hope this helps. Pete wahiggin wrote: I am trying to combined a couple of formulas from posts that I received and keep getting an error message. NETWORKDAYS doesn't work by itself because I have one extra work day, Saturday. So I was trying to combine this: =NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7) and this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) A1 = Start Time B1 = End Time A2 = Task Start Date/Time B2 = Task End Date/Time D range = Holidays (my exmaple only had three holidays so it was D1:D3) To make this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) But I keep getting an error message. Any ideas? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
Well, Dave, would you like to translate that into R1C1 notation? <bg
Pete Dave Peterson wrote: The limit is 1024 characters when measured in R1C1 notation--not 255. Pete_UK wrote: You are limited to 255 characters in a formula - you have 365 in your final formula, so you'll have to approach it differently. Can you split your formula and make use of helper cells? It strikes me that a UDF might be a better approach here. Hope this helps. Pete wahiggin wrote: I am trying to combined a couple of formulas from posts that I received and keep getting an error message. NETWORKDAYS doesn't work by itself because I have one extra work day, Saturday. So I was trying to combine this: =NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7) and this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) A1 = Start Time B1 = End Time A2 = Task Start Date/Time B2 = Task End Date/Time D range = Holidays (my exmaple only had three holidays so it was D1:D3) To make this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) But I keep getting an error message. Any ideas? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
A challenge for an Excel Master...
I'd put the formula in a cell and change tools|options|general tab|and check
R1C1 Reference style. (or use ConvertFormula in code) Pete_UK wrote: Well, Dave, would you like to translate that into R1C1 notation? <bg Pete Dave Peterson wrote: The limit is 1024 characters when measured in R1C1 notation--not 255. Pete_UK wrote: You are limited to 255 characters in a formula - you have 365 in your final formula, so you'll have to approach it differently. Can you split your formula and make use of helper cells? It strikes me that a UDF might be a better approach here. Hope this helps. Pete wahiggin wrote: I am trying to combined a couple of formulas from posts that I received and keep getting an error message. NETWORKDAYS doesn't work by itself because I have one extra work day, Saturday. So I was trying to combine this: =NETWORKDAYS(A2,B2)+INT((B2-A2+WEEKDAY(A2))/7) and this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX(NETWORKDAYS(A2+1,B2-1,$D$1:$D$3),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) A1 = Start Time B1 = End Time A2 = Task Start Date/Time B2 = Task End Date/Time D range = Holidays (my exmaple only had three holidays so it was D1:D3) To make this: =IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),$D$ 1:$D$3,0)))),0,ABS(IF(INT(A2)=INT(B2),ROUND(24*(B2-A2),2),(24*($B$1-$A$1)*(MAX((NETWORKDAYS(A2+1,B2-1,$D$1:$D$3)+INT((B2-A2+WEEKDAY(A2))/7)),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(((24*(B2-INT(B2)))-24*$A$1)+(24*$B$1-(24*(A2-INT(A2)))),2),ROUND((24*($B$1-$A$1)),2)))))) But I keep getting an error message. Any ideas? -- wahiggin ------------------------------------------------------------------------ wahiggin's Profile: http://www.excelforum.com/member.php...o&userid=35013 View this thread: http://www.excelforum.com/showthread...hreadid=547509 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 5 and Excel 2000 question. | Excel Discussion (Misc queries) | |||
Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
Updating master workbook from source that may/may not exist | Excel Worksheet Functions | |||
Excel 2002 Master Template Question | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |