Elapsed Time Minus Holidays

[kod2th3e]

I would like to calculate the difference in time between two dates. Assuming that my company works 24/7 I would like to calculate the difference between an "IN" date/time and "FINISH" date/time skipping over any days that may be in a list of holidays.

So, if in cell A1 I have 9/11/2009 9:23 AM ("IN" date/time) and in cell B1 I have 9/14/2009 9:23 AM ("FINISH" date/time) and I have dates 9/12/2009, 9/15/2009, & 9/19/2009 in H1:H3 ("holiday list") then, I would like Cell C1 to display 48:00:00 because 9/12/2009 is in a list of holidays to skip over.

Otherwise. if 9/12/2009 wasn't in the list of holidays it should read 72:00:00.

If anyone could help with this, that'd be awesome, thanks for your time in viewing.

 

[TFCJamieFay]

Hi,

Try:

=SUM(C2-C1)-COUNTA(HolidayList)

Where "HolidayList" is a named range containing your holidays, C1 is you start date and time and C2 is your end date and time.

HTH,

Jay

 

[kod2th3e]

I get a negative number when this is done, so the cell that this formula is in displays a whole bunch of pound (#) signs.

 

[Andrew Poulsom]

Try:

=(B1-A1)-SUMPRODUCT(--(COUNTIF(H1:H3,ROW(INDIRECT(INT(A1)+1&":"&INT(B1))))<>0))

 

[kod2th3e]

Thank you, works perfectly.

Thanks,
-Cody