Automatic finish date & time with lunch break and bank holidays

[deckerp]

Hi,
I would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this.


The parameters are:
A1 = Start time 08:00
B1 = End Time 17:00
A2 = break lunch 12:00
B2 = back from lunch 13:00

Task parameters

A5 = start date 01/03/10 (entered manually)
B5 = start time 10:00 (entered manually)
C5 = duration 02:00 (hrs entered manually)
D5 = "end date" >>> (to be calculated exluding breaks and holidays)
E5 = "end time" >>> (to be calculated exluding breaks and holidays)


the next line should be filled in automaitically according to the hours needed and the previous end date & time

A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays)
B6 = "start time" (after line 5: to be calculated exluding breaks and holidays)
C6 = duration 14:00 (entered manually)
D6 = "end date" >>> (to be calculated exluding breaks and holidays)
E6 = "end time" >>> (to be calculated exluding breaks and holidays)

and so on for every new line down.

 

[barry houdini]

You seem to have picked up an extra bit on the end of the formula - that third line shouldn't be there - it might have been because I needed to edit that post. The long formula for E5 should be like this

=MROUND(B5+C5-(NETWORKDAYS(A5,D5,H$1:H$100)-1)*(B$1-A$1+A$2-B$2)+(B5< A$2)*(B$2-A$2)-(B5+C5-(NETWORKDAYS(A5,D5,H$1:H$100)-1)*(B$1-A$1+A$2-B$2)+(B5< A$2)*(B$2-A$2)<=B$2)*(B$2-A$2),"0:01")

 

[deckerp]

oh my goodness


I am working and trying to figure out where my mistake is....

simply using a french system and did not realize for a long time that inestead if SI there war IF.....


solved.....
only the hazzle with the order left. Thanks to everybody and good night,
Peter

 

[layanto]

Hi Barry,
Apologies for resurrecting an old thread but I have a question relating to the excel formula you prepared in this thread.

The formula here calculates end date time based on start date time, work hours, duration, weekends and holidays.

How should the formula be modified to calculate the hours so far for a given date and time between the start date time and end date time? My intent is to track the expected progress of task.

Thank you.

 

[James006]

Hello Barry,

Congratulations on your brilliant solution to account for the Lunch break ...!!!

Do you think this "split" version, with Date on one hand and Time on the other hand, could be merged into a single Workday() formula ...???

Thanks a lot for your insights ...

 

[Jocce]

Sorry for "lifting" a really old Thread.
But I cant get this to work, in Excel 2010.

Excel just tells me something is Wrong in the formula?


/Jocce