# Calculation of Datetime

0

Hi Everyone,

I need some help to calculating Datetime for meet SLA & TAT as below:

Start Date/Time : 28/07/2017 15:00

want to add 4 Hours in the above time. But condition is answer should be within Shift time (08:00 am to 17:00pm) and excluding Weekends.

After adding 4:00 hours in the above formula answer wants to come as

31/07/2017 10:00

So please check, I tried one formula but its not giving proper answer.

0

https://www.teachexcel.com/talk/989/calculation-of-end-date-by-using-start-date-and-time-and-working-hours-as-i#discuss_section_1894

0

Nice to see that you found your answer. However, I have been working on this d..n formula for 6 hours and I want to post it anyway. Would have taken about 45 minutes in VBA. Here is the formula.

``=IF(\$C15+(\$B15/24) > (INT(\$C15)+\$D\$3),WORKDAY(\$C15,INT(((\$B15/24)-(INT(\$C15)+\$D\$3-\$C15))/(\$D\$3-\$D\$2))+IF(MOD(ROUND((\$B15/24)-(INT(\$C15)+\$D\$3-\$C15),5),ROUND(\$D\$3-\$D\$2,5)),1,0))+\$D\$2+((\$B15/24)-(INT(\$C15)+\$D\$3-\$C15)-(MAX(INT(((\$B15/24)-(INT(\$C15)+\$D\$3-\$C15))/(\$D\$3-\$D\$2))+IF(MOD(ROUND((\$B15/24)-(INT(\$C15)+\$D\$3-\$C15),5),ROUND(\$D\$3-\$D\$2,5)),1,0)-1,0)*((\$D\$3-\$D\$2)*24)/24)), \$C15+(\$B15/24))``

I copied this from row 15 but you can paste it anywhere in your worksheet. It is intended for your column D. It needs the following.

1. Shift Start Time in D2 (must be in Time format)
2. Shift End Time in D3 (must be in Time format)
3. A Start Date in column C (Must be Date/Time format)
An error may occur and the result will be wrong if you enter a time later than Shift End. But if you enter a time before Shift Start the formula will presume that you work extra time. Don't expect correct calculation if you enter a date which is a weekend.
4. A production time in columm B.
I dispensed with your idea to make this eitehr hours or days. Make it hours for all entries! I suggest you format the cell with a Custom number format, like
``0.00 "hours"``
. So you just enter the number and Excel will add "hours" after it. The formula can't deal with a cell content of "2 Hours". However, if you don't intend to use quarter-hours you can reduce the format to
``0.0 "hours"``
or even
``0 "hours"``

I attach the workbook in which I developed the above formula. If you ever need to modify it you will find this very helpful. Look in rows 21:24, starting from column G to AA. AA has the complete formula.

### Discussion

Thank you very much for your help
Dnyan Aug 10, '17 at 12:40 pm