I need to compare actual time to a deadline and calculate the minutes early or late - and across midnight. For example; if the deadline is 11:50 pm and the actual is 11:55 pm the result is 5 minutes late, if the actual is 11:45 pm it's 5 minutes early. Where I'm having trouble is when the deadline is 11:50 pm and the actual time is 12:05 am (15 minutes late). I've tried several formulas (see below) but none will work uniformly across all three situations. Can someone offer a suggestion?
Some formulas that work some of the time (but not all):
=B1+(A1>B1)-A1
=B1-A1+(b1<A1)
=B1-A1+IF(A1>B1,1)
?? Thanks...
Yes, store the date with the time, then you can easily go across midnight with a simple subtraction.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
If there's not more than a day's difference ever, you can use =Mod(B1-A1,1)
Format the cell as General and put in this formula:
=((B1-A1) + (A1>B1)) * 1440
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks