Excel Function for Difference in times?

Question

I'm trying to come up with an excel function that will tell me the difference between two dates AND times but it needs to calculate only the hours between 7am and 7pm during the weekdays.

Example,

A1 7/24/2008 2:41

A2 7/29/2008 0:06

A1 would start counting at 7am on 7/24/2008 through 7pm on 7/24/2008 as One Day.

I would like cell A3 to calculate the difference between these two times in Days: Hours: Minutes, but only count the hours between 7am and 7pm during the weekday. So A3 would be 3:00:00 (3 days:0 Hours: 0 Minutes)

Is this possible?

Answer 1

If you can guarantee that the start & end times in Columns F & G are between 7:00 AM and 7:00 PM then the formula in Column H for the time difference as Days:Hours:Minutes for Row 3 is the fairly simple

=G3-F3-((INT(G3)-INT(F3))*0.5)

If you can’t make the guarantee, then you will need to have the unrestricted times in Columns D & E and place the more complex formulas below in Columns F & G respectively to get the result in Column H of Row 3 with the same formula still there.

=IF(MOD(D3,INT(D3))>(19/24), INT(D3)+1+(7/24), MAX(D3,INT(D3)+(7/24))) in Column F

=IF(MOD(E3,INT(E3))<(7/24), INT(E3)-1+(19/24), MIN(E3, INT(E3)+19/24)) in Column G

Of course any formulas you put in row 3 would be copied down for as many rows as needed.

Note:  Column H cells need to be formatted as d:hh:mm for the results to appear correctly.  And there are two spaces added to each of the long formulas to make Answers show them correctly.

Answer 2

This website may help you:

http://www.cpearson.com/excel/datearith....