Excel has a feature that allows you to take a formula and convert it to text (copy and paste as a value) and I can’t find it in Numbers.
Trying to convert a value like “2022.12.04 07:50 AM” to date format 12/04/2022. I’ve used the functions CONCATENATE, MID, and LEFT to come up with what looks like it’s right but when I try to create a chart (line graph) it doesn’t work. I might be missing something in the graphing steps. Any help would be appreciated.
I’m trying to plot the data downloaded from my Wyze scale device. X= date and Y=weight. Should be a simple X-Y plot.
—–
Bruce Stevenson
First, if you have all of this data in a text file, I would just search and replace all periods with dashes. This gets you to a format Numbers recognizes as a date.
Even if you don't, you can do a Find & Replace on all periods to dashes, then copy and paste back. If there are other text cells that use periods some other way then it would change those too, but you can always just copy the changed cells, undo to before the change, and then paste back into those cells.
Once you have the text in the cells as "2022-12-04 07:50 AM" just change the Data Format of those cells to Date & Time. Then it will convert the cells from text to an actual date and time value.
If you really want to do it with function instead, start with the DATE function and use MID to get the three parameters for that. Then add the TIME function with the two parts for that. DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+ TIME(... etc.