Calculating time against deadline, across midnight

[jwhacks]

I'm trying to calculate time against a deadline (early or late) in minutes using military time format such as:

Deadline = 15:00
Actual = 15:20
Variance = 0:20

or

Deadline 15:00
Actual = 14:50
Variance = -0:10

However when the actual time crosses midnight the variance is incorrect. So I need to find some type of solution to calculate time variance either before the deadline, after the deadline, or after the deadline when past midnight. I'm stumped. Any suggestions would be gratefully appreciated.

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use eg:

=MOD(A2-A1,1)

 

[jwhacks]

Thanks for the welcome. This forum will be useful.

I’ve tried your suggestion. Here is what I get when trying different actual variables:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
EX 1 Correct
B16 = 17:50 (deadline)
B17 = 17:55 (actual – later than deadline)
B18 = 0:05 (minutes early/late (using =MOD(b17-b16,1))
<o> </o>
EX 2 Correct
B16 = 17:50 (deadline)
B17 = 0:10 (actual – later than deadline and after midnight)
B18 = 6:20 (minutes early/late (using =MOD(b17-b16,1))
<o> </o>
Ex 3 Incorrect
B16 = 17:50 (deadline)
B17 = 17:45 (actual – earlier than deadline)
B18 = 23:55 (minutes early/late (using =MOD(b17-b16,1))
<o>Want this to result in -0:05. </o>
<o> </o>
When the actual crosses midnight the result is incorrect. Any ideas? Thanks.

 

[Andrew Poulsom]

There is no way of telling if midnight has been crossed without entering date and time.

You can't have negative time in Excel without using the 1904 date system, which has disadvantages.

http://support.microsoft.com/kb/214330