# Time subtraction confusion

#### Gates Is Antichrist

##### Well-known Member
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Jonmo1

##### MrExcel MVP
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))

#### Jonmo1

##### MrExcel MVP
The problem is that excel doesn't like negative time #s....

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)

#### Anthony47

##### Well-known Member
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.

#### Anthony47

##### Well-known Member
The "simpler formula" that I tryed to suggest is:
=A2-A1+(A2<A1)

Bye.

#### Jonmo1

##### MrExcel MVP
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.

#### Jonmo1

##### MrExcel MVP
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

#### VoG

##### Legend
To avoid negative times

=MOD(A1-A2,1)

#### Gates Is Antichrist

##### Well-known Member
Once you have your sheet functioning, THEN go and adjust formats for nicer appearance.

Just my opinion.
That's sage 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.

#### Anthony47

##### Well-known Member
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.

Replies
3
Views
681
Replies
4
Views
2K
Replies
0
Views
330
Replies
3
Views
626
Replies
5
Views
459

1,191,177
Messages
5,985,132
Members
439,941
Latest member
robertv13

### 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.

### Which adblocker are you using?

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

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