Calculation of Datetime


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. 

So please help me for this also please check attached file for your referenece.




Yes I got answer myself in below link


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.



Thank you very much for your help
Dnyan Aug 10, '17 at 12:40 pm
If it helps you please select the answer. If the answer you found yourself is better, don't do that. :-) Thanks for the curtesy of your reply.
Variatus (rep: 3728) Aug 10, '17 at 10:15 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login