I need to average time in Numbers, but once past midnight, it’s not accurate. How do I fix this?

The error triangles tell you what they think is wrong. If it is a syntax error it is most likely because you are someplace that uses the comma as the decimal separator. If that is the case, the parameters in a function need to be separated by semicolons, not commas, and the number 0.5 in the formula needs to be 0,5

=IF(A="";"";TIMEVALUE(A+0,5))

Hi YagamiAdachi,

I am not sure of your overall aim, but perhaps the TIMEVALUE function will work for you. TIMEVALUE ignores the date and shows the decimal fraction of a day. Click in a cell and type = to see the Function Browser:

Here we go, with Date & Time formatted as YYYY-MM-DD (to make the Date more "International") and 24 hour time:

Formula in B2 (and fill down) is TIMEVALUE(A2)

Formula in Footer Cell B7 is AVERAGE(B)

I may not have answered your question. Please call back with more details.

Ian.

Following Badunit's suggestion, your average time to start sleeping is 32 minutes and 18 seconds past midnight. Enquiring minds would ask how you record Start Sleeping to such exact hour, minute ,second 🤔.

Data from your post 3 days ago:

Regards,

Ian.

Hi YagamiAdachi,

Averaging Date & Time(instants in time: for example, time of day) is meaningless.

However we can average Durations (elapsed time: for example, finish time minus start time).

A screen shot showing more of your table may help us to help you.

Also, the "crossing midnight" problem is easy to resolve.

Regards,

Ian.

This isn't a simple average, that's for sure.

22:00 and 22:02 averages to 22:01

23:00 and 1:00 "averages" to 0:00

22:59 and 1:00 "averages" to 11:59:30

You also have to strip the dates off to do any average that will make sense. 12:00AM yesterday averaged with 12:00AM today is not "12:00AM", it is halfway between the two date&time values = 12:00PM yesterday. TIMEVALUE strips the dates and gives a decimal number for the time of day. 0 = midnight, 0.5 = 12PM (halfway through the day), and so on up to just about 1.0 and then it becomes 0 again.

Because your bedtimes cross midnight, a simple average of the times of day won't work because you are averaging numbers that are close to 1 (near the end of the day) with numbers that are close to 0 (the beginning of the day) and the resulting average will be sometime in the middle of the day (0.5). We need to shift the clock so your times don't cross that 0 boundary. Shifting forward by half a day should work. Your late night bedtimes will move to just before noon the next day and your early morning bedtimes will be just after noon. Then we shift it back after doing the average.

Column B formula =IF(A="","",TIMEVALUE(A+0.5))

Copy/paste it to all the rows in the column

The average =TODAY()+AVERAGE(B)−0.5

You don't need the extra =

The fx on the Formula Editor is an implied =

Ian.