Time subtraction confusion

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
This will sound like a lot but it goes fast. In a fresh sheet:
Type 23:35 in A1
Type 23:45 in A2
Type 23:55 in A3
Type 0:05 in A4
They should appear just like typed, i.e. custom format h:mm . If not, make them so. Now I want durations so I'll gamble on simple cell subtraction. So B2=A2-A1
and copy to B3 and B4. Naturally, B4 is bogus, so change it to B4=24+A4-A3.

Now you see what looks like 0:10 in all cells in column B (format it the same as column A if your XL version didn't already). If you formatted all column B to h:mm AM/PM they'd all appear as 12:10:00 AM
Now the fun begins. Guess what would be AVERAGE(B2:B3)? AVERAGE(B2:B4)? Answer is at bottom in case you want to challenge yourself.

In either format you'll see a seemingly bizarre answer for AVERAGE(B2:B4).
In fact, the only solution I've found to this seeming anomaly is to use MINUTE(AVERAGE(B2:B4)) instead.
However I lose the nice colon that way (and appropriate expression of 70 minutes if that duration occurred)
Not only that, MINUTE(A2-A1) shows 0:00!

I guess the problem is that time really represents seconds since some point like 1904 or 1970, and that I'm really only seeing modulus. Ah, but if that were true, why does A4-A3 garbage out? Further confusing things is that the "seconds since 19XX" line would suggest that my formula only added 24 SECONDS to A4; but indeed it added 24 hours. (Or did it? Formatting the average as h:mm suggests it added 48!!)

Anyhowwww...I'd like the duration between times in column B, but keep time format. What's the trick I'm missing? Any sage perspective on the contradictions I sensed, or flaws in my remarks?

(average of B2:B3 shows 0:10 or 12:10AM; B2:B4 shows 16:10 or 4:10PM)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try in B4

=1+A4-A3


Or, to keep all formulas in column B the same, and just fill them all down..

in B2 and filled down to B4
=MIN(ABS(1+A2-A1),ABS(A2-A1))
 
Upvote 0
The problem is that excel doesn't like negative time #s....
Adding 24 made it add 24 DAYS, not Minutes hours or seconds.

Since excel calcualtes all times as a fraction of 1.

adding 1 would be the correct way to approach.

Here's a simpler solution than my last suggestion

=IF(A2-A1<0,A2-A1+1,A2-A1)
 
Upvote 0
A simpler formula to set in B2 and copy down is
=A2-A1+(A2<A1)

It adds "1" in case that the end time is lower than the start time.

The error that antichrist made is adding 24 that, giving the excel timing convention, meant 24 days; and the average was not 16h10m, but 7days16h10m, but the days where hidden with the chosen format.

Hope this does not confuse...
Bye.
 
Upvote 0
The "simpler formula" that I tryed to suggest is:
=A2-A1+(A2<A1)

Bye.
 
Upvote 0
If I may make a suggestion to you.

Formatting a cell does not change the value of the cell's contents. It only changes the appearance of the value in the cell.

In your post you were quite explicit to format the cells in a time format.

If you had NOT formatted the cells as time, and just left them in the default format, you would have seen that

A2 - A1 = 0.0069444444444445300000
A3 - A2 = 0.0069444444444444200000
24 + A4 - A3 = 23.0069444444444

thus you would have seen the problem.


so my suggestion is, while in development stages of your sheets, ignore formatting. get your formulas working regardless of the APPEARANCE of the values in the cells. The actual value in the cell is more important to the functionality of your sheet. Once you have your sheet functioning, THEN go and adjust formats for nicer appearance.

Just my opinion.
 
Upvote 0
Another part of the equation I think you've missed is how dates/times are expressed in Excel..

Dates are Whole #s.

1 = Jan 1 1900
2 = Jan 2 1900
3 = Jan 3 1900
etc...
39293 = July 30 2007


Times are FRACTIONS of whole #s

1 Day is 24 hours...So...
1 hour is 1 / 24 = 0.0416666666666667000
1 minute is 1 hour / 60 = 0.0006944444444444440
1 second is 1 minute / 60 = 0.0000115740740740741

So 1.5 in Date/Time language = 1/1/1900 12:00 PM
3 PM 7/30/2007 in NUMBER = 39293.625


Hope this is Helpfull.
 
Upvote 0
Once you have your sheet functioning, THEN go and adjust formats for nicer appearance.

Just my opinion.
That's sage :biggrin: and I appreciate it. The truth is, I fooled myself by that format into thinking it was counting hours. It's not the last time I'll commit that oversight with time/dates so your advice is well taken.

Still, I would have (basically I did) assumed seconds, not days! But .06 would clearly expose that folly.

Anthony I suspect you had a good point that the website would not display correctly. Avoid less than and greater than signs. There's a TEST forum here that might be handy too.

I was aware of the negative issue and actually am checking IF (A1 LESSTHANSIGNHEARGRRR A2 though that MOD solution is intriguing. Anyway in my temporary insanity during this problem I started to think about seconds since 1970 or days since 1904 or whatever. That raises a point - these values are calculating the portion of a day since *the previous midnight.* I need to go back and learn my ABCs.

Sorry for my lunacy, but I believe reading your points will prove beneficial for many readers. Like me. :rolleyes:
 
Upvote 0
Hummmm.... I was sure that my "simpler formula" was correctly shown on my second post.
It is
Code:
=A2-A1+(A2<A1)

that is:
EQUAL A2 MINUS A1 PLUS PARENTHESYS A2 LESSTHAN A1 PARENTHESYS

Bye.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top