Untitled 1.odsMy problem is this: I have a row of 7 times in which some are around 11:00 PM and some are after 12:00 AM. None is later than 1:30 AM. When I do an =AVERAGE(B3:H3), the result is 7:33 AM All cells are formatted for Time, 01:37 PM.
I am using Calc in version 6.4.2.2 under Mac OS 10.15.4.
When I change all PMs to 12:00 AM, the average is where it seems it should be.
Please provide a sample file - since times are fractions of a day (decimal numbers), that should work without a problem. You could test, by temporarily formatting your times as number and check whether all your times are “real calc” times and rule out some being text.
How do I add a file? I reformatted the times as numbers and got long decimals: 0.980555555555556 0.070833333333333 0.0625 0.023611111111111 0.004861111111111 0.998611111111111 0.064583333333333. I just uploaded a file with the offending entries.
And what is wrong with the averages? What values would you expect (can’t recognize an error) ? I suggest you format to a non-US format and you will see that 12:34 AM is 00:34 in other locales (can’t get used to this convention myself - I’m not used to associate a number 12:34 with half past midnight)
Have you considered using a 24-hour clock? Then there is less chance of any ambiguity.
Some people avoid the usual problems with dates and times by using only the 24-hour clock and converting all dates/times to fractional days of the week or of the month or of the year, as their application might require.
I changed all the times to 24 hour notation. The fist time on the line, for example, was 23:32. The That brought the average time to one higher than any of the other times (all of which, being after Midnight, were also smaller than 2. The average was over 4, which did not seem correct, but reflected the size of the just-before-midnight number.
all of which, being after Midnight, were also smaller than 2
You seem to have a day switch in mind what you makes feel, that the averages are wrong. Just take the first 2 values in row one - the average of 23:32 and 01:42 is 12:37 (24 hour notation) and not something like 00:37
Opaque: The two lines on the sample I uploaded come out with averages that don’t make sense. A two minute difference between F1 and F2 yields a difference in averages (compare H1 and H2). I suspect there is a flaw in the spreadsheet I can’t cure by deleting the formatting or reentering the numbers.
No - the difference of F1 and F2 is not 2 minutes. It is 1438 minutes. 00:00 (numerical 0) means start of a day, while 24:00 (numerical 1) is end of a day. That’s why the average correctly calculates to 11:59.
OK, Then ho do I find an average when the field extends from 11:00PM to some time in the morning? A 24-hour clock doesn’t work if one or two of the numbers comes from just before midnight, and most of the numbers are somewhere between 00:01 and 04:0The average is greater than any of the numbers after midnight. As you have seen, the average between, say 11:45 PM and 1:00 AM does not come out to a time between those numbers. Is there any way to get an average time? Some adjustments?
It doesn’t matter how you format times. Your problem is, that you got a day shift in mind, which cannot be dealt with using times only. You need to add additional information to get that reflected. If using times only, there is no way to tell, that you mean 00:45 being after 23:45 - in terms of calc 00:45 is always before 23:45 - absolutely regardless whether you use US-times or 24 hour formats. Internally times are decimal numbers
This seems to show what you have in mind.
Opaque: Thanks for your help!
If one converts all the days and times to fractional days of the month (or week, or year, depending on how long your time cycles are), then all this mess with hours is avoided. For example, for time differences, and averages, within the same month, the following formula works for data in row 2100 (just randomly picked from an active spreadsheet) :
=DAY(A2100)+HOUR(B2100)/24+MINUTE(B2100)/1440Thanks. Looks like it might work (I would have to add a date line to the spreadsheet.
(You’re welcome.) Doing this makes the date/time become a point on the ‘real number line’, if you know what I mean. Then you can add them, subtract them, find an average for a range of them, whatever you need, and all you have to do is convert the final answer back to DAY, HOUR, MINUTE, if that is what you need.
When calculating with time of day only (disregarding the passing of a day), the scale starts and ends at midnight. This means that 11:00pm is later than 1:30am. So if you have one time @10:00pm and one @12:00am, the average of the two is 11:00am, not pm as you may expect.
|.12.1.2.3.4.5.6.7.8.9.10.11.12.1.2.3.4.5.6.7.8.9.10.11.|.12…
To calculate an average for the timespan you want, you must add 1 day to every time which, according to that timespan, is “across midnight”.
A formula with sumifs looks to work fine: =AVERAGEIFS(A1:G1;A1:G1;"<"&TIME(12;0;0)) for AM average, changing to =AVERAGEIFS(A2:G2;A2:G2;">="&TIME(12;0;0)) for PM average.