Derive end date and time from start date and time, office working hours and lunch breaks


Given the following inputs/restrictions, one may want to compute the end date and time of a project:

1. Start date and time of the project; and
2. Official working hours; and
3. Lunch breaks hours

Furthermore, to add to the complication, one may have different National holidays and weekend days i.e. while for some, the weekend days may be Saturday and Sunday, for others it may be Friday and Saturday.

You may refer to both my solutions here

1. Given National holidays and where Saturday and Sunday are the weekend days - Download workbook.  This solution will work in all Excel versions

2. Given National holidays and where Friday and Saturday are the weekend days - Download workbook.  This solution will work only in Excel 2010 and higher versions.

RSS 6 Comments…

 Share your views
  1. Hi Ashish

    Thank you very much for your suggestions, its working fine now.

    Thanks for your time and patience in understanding my problem and giving the solution.
    I am sure that this solution can benfit the other people as well, it is a Miracle.
    Kindly regards

  2. I was wondering if you had an example of a formula that would calculate a completion date based on a period of work hours and a start date formatted as: mm/dd/yyyy hh:mm. To fit within the context of my work, it would need to avoid Holidays and weekends, much the same as your turnaround calculation does, and allow the alteration of working hours, again, as your turnaround calculation does.

    A1 = 4 hours
    B1 = 1/3/2014 5:00 PM
    C1 = ECD
    D1 = 8:00 AM (work start time)
    E1 = 6:00 PM (work end time)
    B2:B9 = Holiday range (B2 specifically being set to: 1/6/2014)

    In this example, the ECD should be calculated to 1/7/2014 11:00. One hour invested between 5:00 PM and 6:00 PM on Friday, Monday (1/6/2014) being skipped because of a declared Holiday, the rest being applied to Tuesday.

  3. Hi Ashish,

    I was doing some entries based on your suggested workbook formulas and I found some interesting results. These are the exact entries in the workbook, all formulas are unmodified and the holiday, start and end times, as well as the lunch times have not been changed. Appreciate your thoughts.

    03/Jan/2014, Friday 5:00 PM 24 08/Jan/2014, Wednesday 9:00 PM
    03/Jan/2014, Friday 4:00 PM 23 09/Jan/2014, Thursday 9:00 AM
    03/Jan/2014, Friday 3:00 PM 24 08/Jan/2014, Wednesday 7:00 PM
    03/Jan/2014, Friday 2:00 PM 48 10/Jan/2014, Friday 10:00 PM

Leave a Comment

Your email address will not be published.