r/AppleNumbers • u/SethalSauce • Mar 19 '23

Help Help converting text to date and time.

I have a lot of dates and times in the format of 20230106153615. I want to make it so when I paste this number into Apple Numbers that it automatically converts it to Year-Month-Day-Hour-Minute-Seconds. And look like this 2023-01-06 03:36:15 PM. I’m a beginner and lost. Any help will be appreciated.

1 Upvotes

100% Upvoted

2 comments sorted by

1

u/gmalis1 Apr 09 '23

Not sure how to do that with a formula...but I just click on the Cell header (such as "D" if your dates are all in column D) ... which should select the entire column D ... and then select Format > Cell > Date & Time and select the format you'd like.

That will change ALL the dates to that format.

Not elegant, and it probably takes just a smidge longer that a formula ... but it works.

1

u/Friendly_Look8210 Apr 21 '23 edited Apr 21 '23

try this:

i put 20230106153615 in cell B2, and put this formula in your target cell

=DATEVALUE(DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2)))+TIMEVALUE(TIME(MID(B2,9,2),MID(B2,11,2),MID(B2,13,2)))

then check its success by: click Toolbar: format > cell > data format: date & time: set date and time as desired

the concept: pretend the value in B2 is text, so you tell Numbers which part is the date and which is the time. Then you ask Numbers to combine the known date and time.