How to calculate expected duration includes non working hours to complete the task

[Wong]

Hi,
My apologies in using long title. I have task received date & time. I would like to use excel to calculate
1) The expected task completion date & time, and
2)The duration of the task to be completed (include non working hours) - how many hours or the total hours from the received date/time to the expected completion date/time.

Working hours is from 9am to 9pm, 7 days a week. Each of the task has to be completed within 9 working hours.

e.g. If the task received on Sun, 13 Mar 2016 05:00:00, the expected task completion date & time will be Sun, 13 Mar 2016 18:00:00. The expected duration will be 12 hours which include the non working hours.

Attached is the excel format for your reference. Appreciate your kind assistance and expertise. Thanks.

 

[NARAYANK991]

Hi ,

What does the +0800 in the following input data signify ?

Sun, 13 Mar 2016 05:00:00 +0800

Narayan

 

[Wong]

Thanks for your reply, Narayan.

+0800 is the Time Zone in Kuala Lumpur, Singapore.

 

[NARAYANK991]

Hi ,

Is that relevant to the calculations ?

I have done some calculations without taking this into account ; see if you can use it as it is.

Narayan

 

[Wong]

Time Zone is not relevant to my calculation.

It's great and definitely it helps.

Narayan, thank you very much.

 

[Wong]

Hi Narayan,

Just wondering what does the Column H represent?

Thanks.

 

[NARAYANK991]

Hi ,

The daily ON time ( the time from 9 AM to 9 PM ) is 12 hours , whereas your targeted delivery duration is 9 hours.

The start time can be in 3 different times of the day :

1. Before the start time of 9 AM
2. Any time between 9 AM and 9 PM
3. After the end time of 9 PM

For these 3 start times , the end time will be as follows :

1. 6 PM on the same day
2. Any time before 9 PM on the same day or the next , depending on the actual start time
3. 6 PM on the next day.

Column H takes care of the situation in 2.

If the value in column H is positive , we add that value to 9 AM to get the end time ; if the value in column H is negative , we add that negative value to 9 PM , thus counting back , to get the end time.

Narayan

 

[Wong]

Thanks!!!