CODEWITHSUNDEEP
excel-formula
Anthony
Anthony

Reputation: 552

How do I deal with a time difference negative value?

In cells K60 and E60 I have two time values and in J60 and D60 I have two date values. The formula below works fine provided the time in K60 is later than the time in E60. If the time is earlier then I receive a VALUE error.

=IF(J60="","",IF(TEXT(K60-E60,"h:mm")>1,(J60-D60)+1,J60-D60))

What formula do I use to resolve this?

Upvotes: 0

Views: 53

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

The problem is that the minus figure can't be converted to a time.
So think in terms of decimal numbers instead.

1 = 1 day
0.5 = 12 hours
0.041666667 = 1 hour
0.000694444 = 1 minute

So:
=IF(J60="","",IF(K60-E60>0.000694444,(J60-D60)+1,J60-D60))

Upvotes: 1

Related Questions