Difference between two dates and times - Specific business hours - EXCEL

0
votes

I need to create a formula to compare two dates, for example: 2015-05-13 18:00:00 and 14/05/2015 08:30:00. As I want to compare the dates only for business working hours (Monday to Friday - 8:00 to 18:00), the difference for this example should be 30 minutes.

I've tried this formula:

=IF(AND(INT(J62)=INT(K62),NOT(ISNA(MATCH(INT(J62),0)))),0,ABS(IF(INT(J62)=INT(K62),ROUND(24*(K62-J62),2),(24*('Business Hours'!$A$3-'Business Hours'!$A$2)(MAX(NETWORKDAYS(J62+1,K62-1),0)+INT(24(((K62-INT(K62))-(J62-INT(J62)))+('Business Hours'!$A$3-'Business Hours'!$A$2))/(24*('Business Hours'!$A$3-'Business Hours'!$A$2))))+MOD(ROUND(((24*(K62-INT(K62)))-24*'Business Hours'!$A$2)+(24*'Business Hours'!$A$3-(24*(J62-INT(J62)))),2),ROUND((24*('Business Hours'!$A$3-'Business Hours'!$A$2)),2))))))

But this formula is not giving me the expected result, as it is returning 10.5.

I have the business hours on a 'Business Hours'A2(Start) and 'Business Hours'A3(End)

1
excelexcel-formula
I think you should split your function. But anyway, try this: cell A1 with 01/01/2015 10:35, cell A2 with 01/01/2015 15:30:00, and cell A3 with =(B1-A1)*1440. In A3 you should see the value 295 (minutes between A2 and A1). Use now the formula in your Excel. - Tarod

1 Answers

0
votes
=IF(DAY(A1)=DAY(B1),B1-A1,(B1-8/24)-(A1+6/24))

If they are not on the same day you'll shift the time up to midnight of the first date and shift it down to midnight of the second date.
It does not keep track of working days. You'll have to figure dividing by seven out by yourself.

Edit: it just hit me there's a function for that.

=IF(DAY(A1)=DAY(B1),B1-A1,(B1-8/24)-(A1+6/24)+(NETWORKDAYS(A1,B1)-2)*10/24)

Edit2: Thanks to the remarks my boneheaded self has managed to understand part of the issue. This function should work. It has safeguards against late issuance and of tasks and early finishes but not against early issuance and late finishes. Feel free to combine them as you wish.

=(NETWORKDAYS(A1,B1)-1)*10/24-(MIN(A1-ROUNDDOWN(A1,0),18/24)-8/24)+(MAX(B1-ROUNDDOWN(B1,0),8/24)-8/24)