convert decimal number resulting from timevalue function back into a time

There is no "time" without a date. To convert the decimal fraction of a 24 hr day back into a Date&Time value using today as the date,

=TODAY()+.875

TODAY gives todays date at 12:00:00AM. That is the default time when you create a date in Numbers, whether you use the TODAY function or create one some other way (except for NOW which includes the current time).

This also works:

="7/20/2020"+.875

Hi Guy,

Try this:

Column A contains a set of numbers corresponding to the days of the month being averaged. These are for the user's convenience, and play no part in which calculates the average of the TIMEVALUES calculations.

Column B contains a list of Date and time values, entered as Times at bed time each nght. Numbers will set the date part of the date and time values to the date on which the entry is made, but, as with the numbers in column A, these dates do not enter into the calculations.

Column C contains the formula sown below the table, entered in C2 and filled down to the rest of the cells in that column.

C2: IF(TIMEVALUE(B2)<0.5,TIMEVALUE(B2),TIMEVALUE(B2)−1)

IF the time part of the value i column B is in the AM range, the TMEVALUE will be less than 0.5, and the formula will place that TIMEVALUE in the cell containing the formula. IF the time isin the PM range, the formula will subtract 1 from that TIMEVALUE (resulting in a negative value) and place that result in the cell.

D2 contains this formula:

D2: AVERAGE(C)

This calculates the AVERAGE of the TIMEVALUEs calculated in column C.

D3 contains this formula:

D3: TODAY()+D2

This returns a date&time value of today's date at the time of day indicated by the average TIMEVALUE in D2.

The cell is formatted for date & time, with only the Time part displayed.

As written, the AVERAGE formula may return a false average when not all dates in the month have entries. Wrapping the C2 formula (shown in bold below) with the 'switch' (shown in normal type weight) prevents the calculation when column B is empty, and places a null string ( "" ) in the cell. This text value is ignored by AVERAGE, so the value in D3 is the correct average bedtime for the dates so far recorded in the table.

C2: IF(LEN(B2)<1,"",IF(TIMEVALUE(B2)<0.5,TIMEVALUE(B2),TIMEVALUE(B2)−1))

Regards,

Barry

PS: In the example the time entries in the yellow block of cells are repeated in the rest of column B. Less typing that way.

B.

iGuy3 wrote:

I’m still wrestling with trying to get a monthly average of my bedtimes. Works fine if I always go to bed after midnight. But, when I hit the sack at 10 PM once, and after midnight every other night, the resulting month average looks incorrect.

You can try something like this:

SG

That is very similar to what I was trying and failing at. It works for times that span midnight but fails for other cases. The average of 11AM and 1PM should be 12PM (noon) but is midnight. The average of 10PM and 11PM is 10:30PM the day before. I wasn't able to come up with a universal formula.

Here is maybe one way to do it that is more universal. Starting with your table with the Date&Time values in column B,

Column C =TIMEVALUE(B)

Column D =IF(C>0.5,C−1,C)

The average time, with today's date attached, would be

=TODAY()+IF(STDEV(C)<STDEV(D),AVERAGE(C),AVERAGE(D))

I cannot say for sure if this works for every case. Averaging values that go around in a circle requires some interpretation. The average of 11PM and 1AM could be equal to 12AM or 12PM; either is a correct answer numerically. I tried to use the standard deviation to bend the answer toward the smaller arc of the circle.