Adding time to a date excluding weekends and holidays

[ToddG]

I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. Any input would be appreciated.

I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.

Has anyone done anything similar to this or could point me to some good info on it?

Thanks very much in advance

 

[barry houdini]

So you said that "I set working hours per day from 08h00 to 05h00" the latter will need to be 17:00 if the format is hh:mm

 

[ToddG]

I'm getting a division error

 

[barry houdini]

I'm getting a division error

You need to have times in D2 and E2, e.g. 08:00 and 17:00 to define the working day. If those are blank (or if D2 = E2) then you'll get #DIV/0!

 

[deckerp]

format is :
hh:mm

So you said that "I set working hours per day from 08h00 to 05h00" the latter will need to be 17:00 if the format is hh:mm

that is what I have done. still the same.

 

[barry houdini]

OK I know it doesn't help much but it certainly works for me!

I have 12-feb-10 16:00 in A1, 12:00 in B1 and D2 and E2 are 08:00 and 17:00

C1 has the formula and the result is 16-feb-10 10:00 as expected

What do you have in A1 and B1?

 

[ToddG]

You need to have times in D2 and E2, e.g. 08:00 and 17:00 to define the working day. If those are blank (or if D2 = E2) then you'll get #DIV/0!

Uggghhh...I had them in D1 and E1. That fixed it.

 

[deckerp]

OK I know it doesn't help much but it certainly works for me!

I have 12-feb-10 16:00 in A1, 12:00 in B1 and D2 and E2 are 08:00 and 17:00

C1 has the formula and the result is 16-feb-10 10:00 as expected

What do you have in A1 and B1?

can I not separate the date and time into two cells ?

 

[barry houdini]

Yeah, you can do it that way if you want, in fact it's a shorter formula. The previous method was just the way I did it for somebody else who specified time and date in one cell.....

If you have start date in A1, start time in B1, hours in C1 and MF start and end times in D2 and E2 then the formula for out put of end date/time in one cell would be

=WORKDAY(A1,CEILING((C1+B1-E$2)/(F$2-E$2),1)-1)+B1+C1-CEILING(B1+C1-E$2,F$2-E$2)+F$2-E$2

or if you want the output in separate cells too then you can just split the formula in half, i.e. for date

=WORKDAY(A1,CEILING((C1+B1-E$2)/(F$2-E$2),1)-1)

and for time

=B1+C1-CEILING(B1+C1-E$2,F$2-E$2)+F$2-E$2

As before the start date and time should be within the working hours defined in D2:E2

 

[deckerp]

-thanks a lot - I got it.

 

[barry houdini]

Sorry I pushed everything across to accommodate an extra cell for time, I gave you the wrong information on D2 and E2......

The start date is A1, start time is B1 and hours to add in C1, the formula should go in D1.....and E2 and F2 are the start and end times of the working day, so using the same as before E2 would be 08:00 and F2 17:00