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 10 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

  4. Abdulrahman Assabri August 20, 2015 at 8:26 pm

    Hello Ashish,

    I really appreciate the amazing work you have done, you made my day, thanks a million.

    I used your sheet as a base, and applied some more dynamic configuration elements, such as, selection of the weekend, either Sat-Sun, or Fri-Sat, Handling the holidays from a range of cells, some additional handling for the start date if it is a weekend or a holiday (was handled based on a single cell within your sheet), and given that the purpose was to handle SLA incidents, another mode of operation was included for the tickets that require 24/7 operations.

    Following is a link to a sample workbook, hopefully, you will find it useful.!69741&authkey=!AIktOJ9AS3Ct-tA&ithint=file%2cxlsx

    Thanks again, and have a great day.



  5. Thank you for the reference and workbook. However, I have two challenges, even though performance computation is on weekdays, start days should allow for weekends. Also, can it have date and time in one cell?

    • Hi,

      You are welcome. My formula should work even if he start day is a weekend. Please try to enter weekends as start days. If you face any issues, please post back. Once this aspect is checked for, we can customize the solution to get the date and time in the same cell as well.

Leave a Comment

Your email address will not be published.