Time subtraction confusion

[Gates Is Antichrist]

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)

 

[Jonmo1]

Personally, I like Vog's Solution

=MOD(A1-A2,1)

Wish I had thought of it...
ray:

 

[VoG]

Personally, I like Vog's Solution

=MOD(A1-A2,1)

Wish I had thought of it...
ray:

I 'stole' it from barry houdini

 

[Gates Is Antichrist]

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.

You know, I believe that VBA's single variable type lulled me into believing that time is only different in format/display representation. Like I said, I need to retool my schooling.

The funny thing is that I've parsed hours seconds and minutes and calculated totals on the side just fine. I guess this is the first time I did math on the time values directly. I'll start by hitting Chip's "drunk cousin" article on DATEDIF

I have noticed a simple workaround for even having to bother with negative timespans, at least from midnight rollover. Adding date to begin with lets everything work exactly the same, but no negatives to contend with (from midnight rollover, that is). (Then time format conceals the date from display, as I want it here.)

 

[Jonmo1]

Yep, working with Dates and Times in Excel can cause major headaches. Not even mentioning how to deal with Leap Years...

 

[Gates Is Antichrist]

Personally, I like Vog's Solution

=MOD(A1-A2,1)

Wish I had thought of it...
ray:

I 'stole' it from barry houdini

Yes that's impressive. For anyone who might not be aware, stick a 1- (One minus) in front to fit my original scenario.
=1-MOD(A1-A2,1) (or just reverse A1, A2)

 

[Jonmo1]

Then time format conceals the date from display, as I want it here

Honestly, not to beat a dead horse but..
You should really stop thinking that way....

because Formulas couldn't care less how you have your times formatted. Formulas read the ACTUAL value in the cell. You'll create more problems for yourself thinking that way.

The MOD method is a really great way to deal with negative Times. It devides the result of your calculation by 1 and returns the reminder. Basically just leaving the Decimal Value (which is the time). The numbers to the LEFT of decimal point are DATES, the numbers to the Right of the Decimal Point are the TIMES.

 

[Gates Is Antichrist]

Since you mentioned it, maybe I "over abbreviated" the point.

What I was describing was that by combining date into the value, you avoid essentially subtracting 0-23 (-ish) which is technically disallowed in date/time arithmetic. Excuse me, I meant you don't subtract 0 minus 23-24ths. Think of subtracting 1890 from 1968 rather than subtracting 90 from 68 (woohoo - W2K workaround). Since (or I should say if) date is available, then
1/2/07 0:05
minus
1/1/07 23:55
is a clear 10 minute subtraction. Obviously when you have to handle the negative span case, you have to handle it. I'm just saying (thrice, actually ) that for simply the matter of _rollover_, this is a simple - okay, call it "lazy" :wink: - workaround to THAT.

Thanks for encouraging me to be more verbose. I assumed that anyone could fill in the blanks