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

{ 25 Comments }

By Ashish Mathur · · · Leave a comment

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.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • 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

    Reply

  • 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.

    Example:
    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.

    Reply

  • 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

    Reply

  • 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.

    https://onedrive.live.com/redir?resid=35f0b07e01fcabd1!69741&authkey=!AIktOJ9AS3Ct-tA&ithint=file%2cxlsx

    Thanks again, and have a great day.

    Regards,

    Abdulrahman

    Reply

  • 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?

    Reply

    • 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.

      Reply

      • Hi, can you tell me if the start date being a weekend issues was resolved? I have looked at your work book and don’t think it works if the start date is a weekend. I need this functionality also.

        Thanks
        Rachel

        Reply

          • Hi,

            I am hoping you can help as I am tearing my hair out! Thanks in advance.

            I am looking to determine if a contractor is meeting his service level obligations by responding to jobs within the correct time. This is made a bit more difficult as there are different job priorities, and the response time can change depending on if they are due to be completed inside or outside access times.

            Is there a way I can attach a spreadsheet?

            Thanks again
            Rachel

          • Hi,

            Upload the workbook to OneDrive/Google Docs or such similar services and share the download link here. Please be very descriptive in your question and also show the expected result.

          • Hi,

            I cannot understand your question. How have you derived the result in cell H12,H14? Also, will the entries in range F12:F16 be inputs already provided or do they have to be derived. If to be derived, then how?

          • Hi,

            I derived the entries in range F12:F16. (I have included this formula in the updated attached spreadsheet) This is for Reported time though and I am not sure if it needs to be used at all.

            The entries in range H12:H16 are my manual calculations of what I would expect to see if I could write a formula to add the rectification hours to the reported time.

            What I need to do is –
            For level A and B jobs – if the expected rectification time falls outside access hours then the contractor either has 2hrs (level A) or 3 hrs (level B) to rectify. If the expected rectification time falls within access hours then the contractor either has 30mins (Level A) or 2hrs (Level B) to rectify. I need to add the applicable time to the reported date and time.

            For level C and D jobs – rectification only has to be completed within access times and the contractor has either 48hrs worth of access time (Level C) or 7 days worth of access time (Level D). I need to add the applicable time to the reported date and time.

            Essentially I want to calculate the date and time each job MUST be rectified by, then I will look at how long it has taken the contractor and work out if they have failed. There are thousands of jobs each month so I am hoping I can automate.

            Updated spreadsheet –
            https://drive.google.com/open?id=0Bz9sMM6KXqgbWUNKcmRIcVM4WVE

            Thanks

  • Hi Ashish, do you need me to provide any more information? I hope that I haven’t confused you with what I am trying to do!

    Reply

  • Thanks,

    I don’t have an Office 365 account but will try and create one so I can have a look.

    I appreciate your help.
    Rachel

    Reply

      • Hi Ashish,

        I don’t get a download option – I just get copy hyperlink, open hyperlink etc. Everything I do takes me to a Sign in to your account page for Office 365. Even though I have now set up an account, it won’t let me sign in!

        Is there another option for me to be able to open your spreadsheet?

        Thanks
        Rachel

        Reply

  • In point 2, downloaded the excel template and added 13 hours as the work duration, I also changed the working hours as 6AM – 6PM, the end date is correct but the end time is wrong, it is showing as 7PM where in fact I want it to show as 7AM as there is one hour to be worked on the next day from 6AM to 7AM. BTW, is there any chance the date and time can be in one cell instead of two. Thanks

    Reply