Date Extraction and then time calculation between two dates

Hi all,

I have the following date format in one cell (examples):
Dec 28 2017 11:01AM
Jan 3 2018 2:25PM
Mar 2 2017 12:12PM

As you can see, they vary in text length so I can't use left, right or mid, at least I think I can't

I need to extract the date and time from those cells. After I extract it, I then need to find the difference between the times to the second. In my example, the difference for example from Dec 28 2017 11:01AM to Jan 3 2018 2:25PM in seconds which I would then convert to days, hours, minutes and seconds. Please help.

The times are being seen as Text (i.e. with the date in A1, =ISNUMBER(A1) returns FALSE)?

If so,

=SUBSTITUTE(SUBSTITUTE(A1," ",", ",2),RIGHT(A1,2)," "&RIGHT(A1,2))+0 will convert the text date and time to an actual date and time.

Thank you for the prompt response, so that worked for the first date, but not the second date, got a Value error. Could it be the text length?

No, the length doesn't matter. It worked on all 3 for me.

See attachment.

Oh no, then there is more to the problem then. I just looked closely at my data and there is extra spaces for the potential date and time fields. For example: Jan _3 2018 _2:25PM and those underscores are blanks. I hadn't seen them. So in your sheet, you are correct, the text length doesn't matter but on mine, since it has extra blanks spaces, it's breaking.

So the data is:
Dec 28 2017 11:01AM
Jan 3 2018 2:25PM
Mar 2 2017 12:12PM

I just realized the forum is removing the extra space, in the data above but again it would be Jan _3 2018 _2:25PM but the underscores are blanks.

Sometimes this forum trims excess spaces and combines them into one. That is probably why posts 1 and 6 are showing single spaces.

So just to be clear you are saying that there are 2 spaces after the month, 1 space after the day, and 2 spaces after the year?

In that case, try this:

B1 =TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",", ",3),RIGHT(A1,2)," "&RIGHT(A1,2)))+0

Maybe try with Get&Transform
Duration format: d.hh.mm.ss

(IMHO, data should be better organized )

Originally Posted by 63falcondude

Sometimes this forum trims excess spaces and combines them into one. That is probably why posts 1 and 6 are showing single spaces.

So just to be clear you are saying that there are 2 spaces after the month, 1 space after the day, and 2 spaces after the year?

In that case, try this:

B1 =TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",", ",3),RIGHT(A1,2)," "&RIGHT(A1,2)))+0

I'm sorry that I haven't put it more clearly. So for example, if it's Jan 12 then there is only one space between Jan and 12. If it is Jan 2, then there is two spaces between Jan and 2 because they system my client uses I guess is leaving an extra space in case it was double digits. The same with time. So maybe this might help. Jan__2_12:20PM or Jan_12__2:20PM or Jan_12_12:20PM

Okay, it will be best for you to upload an actual Excel workbook with a representative sample of your data in it. That way we don't keep playing ping pong until I guess right.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Originally Posted by 63falcondude

Okay, it will be best for you to upload an actual Excel workbook with a representative sample of your data in it. That way we don't keep playing ping pong until I guess right.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Ok I attached

Originally Posted by sandy666

I'll try again

Hi Sandy, although the desired output so thank you, I need to run it on various reports and have it set up as a formula for daily data. I wouldn't even know where to begin with get and transform since I have zero experience with that

Thank you for posting that though

Based on the workbook in post #12, put this formula in C6:

=SUBSTITUTE(SUBSTITUTE(TRIM(A6)," ",", ",2),RIGHT(A6,2)," "&RIGHT(A6,2))+0

Drag it over to D6 and then down.