Calculate End Date & Time from Start Date & Time for given Duration (Total Work Hours), in Excel
Determine End Date / Time (Cell A8) from Start Date / Time (A1) for Total Work Hours Duration (D1) per given: (i) Daily Start & End Time (B1 & C1), (ii) Daily Lunch Start – End Time (E1 & E2) and (iii) Exclude Weekends and Specified Holidays (F2:F15)..
Cell A8 formula: Returns the End Date and Time.
Cell A1 has Date & Time Format: “3/4/12 13:30”
Cells B1, C1, E1 & E2 have Time Format: “1:30 PM”
Cells F2:F15 (contain only a date / integer) have Date Format: “3/4/12”
Cell D1 has “General” Format.
Cell A8 (Formula Cell) has Date & Time Format: “3/4/12 13:30”
———————————————————————————————–
Calculate End Date from Start Date and Duration
Cell A8 formula: Returns the End Date and Time from Start Date / Time (A1) for Total Work Hours Duration (D1) per given: (i) Daily Start & End Time (B1 & C1), (ii) Daily Lunch Start – End Time (E1 & E2) and (iii) Exclude Weekends and Specified Holidays (F2:F15).
Cell A8 formula:
=MAX(WORKDAY(INT(A1), ROUNDUP(ROUND(D1- MIN((MAX(C1- MAX(MOD(A1, 1),B1),0)+MIN(( MAX(MOD(A1, 1), E1)-E2), 0)), D1/24)*(WEEKDAY( A1, 2)<=5)*(COUNTIF(F2:F15,INT(A1))=0)*24,6)/ROUND(((C1-B1)-(E2-E1))*24,6),0),F2:F15)+B1+(ROUND(E1-B1, 6)<ROUND( MOD((D1 -MIN(( MAX(C1- MAX(MOD(A1, 1),B1),0)+MIN(( MAX(MOD(A1,1), E1)-E2),0)), D1/24)*(WEEKDAY(A1, 2)<=5)*(COUNTIF(F2:F15, INT(A1))= 0)*24)/(((C1-B1)-(E2-E1))*24), 1)*((C1-B1)-(E2-E1)), 6))*(E2-E1)+MOD((D1-MIN((MAX(C1- MAX(MOD(A1, 1), B1), 0)+ MIN(( MAX(MOD(A1,1),E1)-E2),0)), D1/24)*(WEEKDAY(A1, 2)<=5)*(COUNTIF(F2:F15, INT(A1))=0)*24)/(((C1-B1) -(E2-E1))*24), 1)*((C1-B1)-(E2-E1)), INT(A1)+MAX( MOD(A1,1), B1)+((ROUND( MAX(MOD(A1, 1), B1)+D1/24, 6)>ROUND(E1, 6))*MAX((E2- MAX(E1, MOD( A1,1))), 0)*24+D1)/24)
Explanation – Cell A8 formula (Non-Array formula):
The formula has been explained in detail, and stepwise, in the Ebook whose link is given below. It may not actually be as complex to understand as it appears because the formula has been broken-up into multiple parts for easy illustration.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
The formula in the next tab of the Ebook calculates backwards, ie. calculates the Start Date and Time from End Date and Time for given Duration: Determine Start Date (& Time) from End Date (& Time) and Duration (Work Hours), per given Daily Start & End Time, Lunch Break, excluding Weekends & Holidays