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

 

[deckerp]

I have implemented the above formula.

however, there is three things missing.

1. I make a break between lets say 12h00 and 13h00 (flexible, and only 1 break).

2. if one task finishs the day at 17h00 then the next in the line below should not start this day at 17h00 but on the following morning according to the start time set (08h00).

3. bank holidays

Thanks for your help.

 

[barry houdini]

I posted a reply to your thread here

 

[rajeloke2]

Here's an example of what you can do if you just define the MF working times....

If you have your start date/time in A1 then hours to add in B1 (in time format e.g. 36:00 for 36 hours) then this formula in C1 will give end date/time

=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2

where D2 and E2 shows the start and end times of the working day, e.g. 08:00 in D2 and 17:00 in E2

doesn't take account of breaks in the day

Note: WORKDAY is an Analysis ToolPak add-in function (unles you're using Excel 2007)

Hello,
This formula is working fine for me.Can you help me with same formula where the weekends are Thursdays and Fridays.

 

[barry houdini]

In a "regular" workday formula if the weekend is still two consecutive days then you can just "offset" the days, e.g. instead of

=WORKDAY(date,days)

you can use

=WORKDAY(2+date,days)-2

That will move the weekend back by two days, i.e. to Thu/Fri so in my suggested formula in this thread you can do the same, i.e.

=WORKDAY(A1,2+CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2-2

 

[rajeloke2]

Thanks.It is excluding the weekends.
Currently My data is as below.

A1 Start time of Task1 - 26-Apr-Tue 8:00 AM
B1 Effort time Currently a 8:00 hrs ( but it takes value as 8:00 AM)
C1 End time of task 1 - 27-Apr-Wed 8:00 AM
A2 Start time of task 2 - 27-Apr -Wed 8:00 AM ( =C1)

But I have the following queries.

1. When I enter 8 hrs, B1 should be 26-Apr -Tue 4:00 PM . Can I enter an integer (8) instead of 8:00
2. A2 should be 27-Apr -Wed 8:00 AM
3. If I enter an effort of 12 hrs for a task, it should be 1 day + 4 hrs

 

[mallm61]

Barry,

The formula worked great for me.

Thank you for the help.

 

[alexp85]

Hi Barry

Have just found this formula by you and is great for what i need except is there a way for it to work if also if a start time is outside of the working hours?? i.e. Time logged/start time is 7pm but working hours are 0800-1700 and the task is 8 hours - so need effectively the clock to start ticking at 8 am the following day with a target finsih time of 1600?

Any help would be greatly appreciated!

 

[psmith500]

hi barry, i cant enter any duration over 12 hours without the cell changing to a date format and returning 00:00?