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.

• oem7110 says:

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

• Randy says:

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.

• Randy says:

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

• Hi,

Thank you for pointing out that error. Please refer to my revised solution in this workbook.

Hope this helps.

• Abdulrahman Assabri says:

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.

Thanks again, and have a great day.

Regards,

Abdulrahman

• Hi,

You are most welcome. Glad to help. Thank you for sharing your workbook.

• Simon says:

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.

• Rachel says:

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

• Hi,

Please take an example. Share some data and also show your expected result.

• Rachel says:

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,

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

• Rachel says:

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.

Thanks

• Rachel says:

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!

• Hi,

You may refer to my solution at this link.

Please note that this is not a perfect solution but will head you in the right direction.

• Rachel says:

Thanks,

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

Rachel

• Hi,

• Rachel says:

Hi Ashish,