ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   A challenge for an Excel Master... (https://www.excelbanter.com/excel-discussion-misc-queries/91656-challenge-excel-master.html)

wahiggin
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

Peo Sjoblom
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

wahiggin
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

wahiggin
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

Pete_UK
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

wahiggin
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

Pete_UK
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

Dave Peterson
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

Pete_UK
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

Dave Peterson
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


All times are GMT +1. The time now is 12:41 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com