How can a date and time formatted as such "2015-10-19 10:47:23 -0700" be used in Numbers?
I want to plot data exported from iOS Health and this is the time format.
I have converted from XML to CSV.
Thank you for any suggestions.
iMac 27″ 5K, macOS 10.15
To convert to a date-time string that Numbers can recognize you need to lop off the GMT offset at the end.
One way to do that is like this:
With the imported string in A2, the formula is this:
=LEFT(A2,LEN(A2)−6)+"0h"
The 0h is added so that Numbers treats the result as Date-Time rather than text. It will then become right-aligned by default.
Once you have the date-time strings in a column, "remove the formula" by selecting the cells, typing command-c to copy, followed by Edit > Paste Formula Results. Then you can delete the original imported values if you want and use the new column for your chart.
SG
To convert to a date-time string that Numbers can recognize you need to lop off the GMT offset at the end.
One way to do that is like this:
With the imported string in A2, the formula is this:
=LEFT(A2,LEN(A2)−6)+"0h"
The 0h is added so that Numbers treats the result as Date-Time rather than text. It will then become right-aligned by default.
Once you have the date-time strings in a column, "remove the formula" by selecting the cells, typing command-c to copy, followed by Edit > Paste Formula Results. Then you can delete the original imported values if you want and use the new column for your chart.
SG
SoCalMtnBiker wrote:
Hadn't done any string manipulation in Numbers; but even if I had known how the coercing to time would have been beyond me. Does the +"0h" coerce it to time by adding 0 hours?
Yes, you could have added "0h" "0m" "0s" "0d", any of the shorthand for entering a Duration. When Numbers finds a string that looks like Date-time and is told to add a Duration to it, it is smart enough to know that you want a Date-Time as the result.
Here are some more ways you could have do the string manipulation:
SG
"Does the +"0h" coerce it to time by adding 0 hours?"
Yes.
The part before the + sign is a standard date and time string. The 0h after the + sign is a recognized format for duration value of 0 hours. With suitable values before and after the +, Numbers is happy to do the math.
Regards,
Barry
Thank you. Hadn't done any string manipulation in Numbers; but even if I had known how the coercing to time would have been beyond me. Does the +"0h" coerce it to time by adding 0 hours? Seems it must because without it Numbers doesn't know it's a date-time even if I change the format to match.
Thank you. Added to my notes.
2015-10-19 10:47:23 -0700 to date and time usable by Numbers macOS
