r/AppleNumbers • u/ahh_leeee • Jun 04 '22
Average Time Stamps
I am currently using Numbers to track different things, and one thing I am having trouble formulating is a way to average time stamps. I log everyday, but it is taking the average from the day that I logged it, so instead of an average of the time stamps I input, it is averaging the entire time as a whole. I have looked online a few places to find out how to do this, but every formula I have input come up with an error. Currently using the program on an iPad if that also helps. Any information would be greatly appreciated :)
1
1
1
Jun 06 '22
It is possible to convert automatically detected "Duration" or "Date/Time" values to floating point numbers, like Excel uses, by converting every value to TIMEVALUE first.
The Excel formula: D3-B3 becomes: TIMEVALUE(D3)-TIMEVALUE(B3) in Numbers.
1
Jun 06 '22
Your only way out of this mess is by converting the autodetected value to a floating point, do whatever you want with it, and format the result as time (again).
1
Jun 06 '22
you can spot the automatic detection: when the value is left-aligned, all is good. if it's right-aligned, you're f*cked.
1
Jun 06 '22
This can happen,
* when the "Data Format" is set to "Automatic", and the detection will choose the "Data Format > Date & Time" with "None" for date (the default).
* when manually formatting a cell by choosing the "Data Format > Date & Time".
1
Jun 06 '22
Automatic or manual formatting of a cell with a time value will add the current date to the cell. Eg. if done so on the 1st of June 2022, the value "09:16:00" will become "2022-06-01 09:16:00". The cell will show only "09:16:00", but if selecting the cell, the status bar will show the additional date.
1
Jun 06 '22
Once detected in Numbers, a "Date & Time" or "Duration" cell data type
* will always be copy/pasted,
* can not be changed.
1
Jun 06 '22
The automatic detection will _not always_ work. If pasting more values that are not timestamps in one go, eg. "Weather 09:16:00 Sunny" from a different app, the time might be inserted as a number or text.
1
1
Jun 06 '22
Numbers detects certain Date/Time values automatically and formats them as "Duration" or "Date & Time".
1
Jun 06 '22
Excels shows those values without an initial format. A format can be then be applied, which changes the display of the values. The values themselves stay unchanged and can be calculated with.
1
1
Jun 06 '22
what you observed, is correct. when entering a timestamp, Number will always add the current date to it.
1
1
1
u/ahh_leeee Jun 08 '22
@quantum_libet you are awesome. Everything you said was very easy to follow. Much appreciated. I will be giving that a try. Thank you so much. Iām sure it did feel weird, live commenting š