Calculation of Datetime

Yes I got answer myself in below link

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

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.