Date format

[StormyGurl]

Hi, Everyone!

I need your expertise with date format issues I am having.
I download data from iPhone messages and paste into sheet “dump” where I use it to create one string of text. Then I copy it and paste it into sheet “Alarms”. What I am trying to accomplish is to extract a date that I can format into a usable date format so I can subtract to dates to get elapsed time between Trip & Restore.

Thank you, take care and stay safe!!!

[Paul]

Can you use:

=DATEVALUE(LEFT(B3,10))
=TIMEVALUE(RIGHT(B3,5))

Format the datevalue as Date (e.g. Short Date)
Format the timevalue as Time

Or customize the formats of the output. Dates and Times are whole/decimal numbers in Excel.

May 26, 2021 = 44342
11:00 AM = 0.4583

So May 26, 2021 at 11:00am would be 44342.4583

[Rick Rothstein]

Just add 0 to your formulas and then format the cell to display the way you want. For example, where you have...

=LEFT(B3,10)

change it to this...

=0+LEFT(B3,10)

Do the same for you time formula.

[MrShorty]

The first thing I did was put =ISTEXT(B3) into a convenient cell and copy down to see if all of these dates are text strings or real numbers/dates. They appear to all be text strings, so I select column B -> Text to Columns -> Delimited (Next) -> Next -> At step 3 of the Text Import Wizard, specify MDY dates for this column of data -> Finish. The date/time stamps are all converted to numbers. It appears that number formatting has some of them formatted as yyyy/mm/dd and others are m/dd/yyyy and some include a time of day in the number format. It all looks correct.

At this point, elapsed time can be calculated as =B4-B3 (copied down every other row) will calculate the time difference. Format as an elapsed time format ([hh]:mm or [mm]:ss or similar) to see the time difference in a more desirable format (or leave it unformatted where the result means "days").

Questions?