User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Calculate End Date & Time from Start Date & Time for given Duration (Total Work Hours), in Excel

 

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

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

          

"Open Image in New Tab" for a full and clear view. 

 

 

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.

 

 

 

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 

 

 

  

    

Only one of the multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.