Splitting and Changing the format of a cell containing dates and times

[Barking_Mad]

Hi,

I download an excel document which has a column of dates and times in it, in the one cell.

12-May-2021 11:58:53

I needed to split this up into two different cells and did so using:

=TRUNC(A1)

for the date

=MOD($F14,1)

for the time

I then compared the split date and time cells to two other cells containing the deadline date and time. A calculation then worked out if they were late (depending also on if the item was granted an extension). However the company has changed the format of the download document and as a result the the date/time format has changed as below:

11 Nov 2021 - 10:00

As a result the splitting of the cells don't work and neither does the calculation. Anyone any ideas how I can split this to get it to work again? Ive attached an example workbook with two dates in, one old style which works, one new one which doesnt. Any help much appreciated.

[Glenn Kennedy]

G14, copied down:
=LEFT(F14,11)+0

H14, copied down:
=ABS(LOOKUP(10^10,-RIGHT(F14,ROW($1:$100))))

see file.

[Pete_UK]

You can get the date with this (your titles seem in the wrong order):

=DATE(MID(F14,8,4),(SEARCH(MID(F14,4,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3+1,LEFT(F14,2))

and the time with this:

=TIMEVALUE(RIGHT(F14,LEN(F14)-FIND("-",F14)))

Hope this helps.

Pete

[Barking_Mad]

G14, copied down:
=LEFT(F14,11)+0

H14, copied down:
=ABS(LOOKUP(10^10,-RIGHT(F14,ROW($1:$100))))

see file.

You absolute legend! Cheers Glenn! reputation x 1000 to you

[Barking_Mad]

You can get the date with this (your titles seem in the wrong order):

=DATE(MID(F14,8,4),(SEARCH(MID(F14,4,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3+1,LEFT(F14,2))

and the time with this:

=TIMEVALUE(RIGHT(F14,LEN(F14)-FIND("-",F14)))

Hope this helps.

Pete

Thanks Pete, that's most useful and well spotted

[Pete_UK]

You're welcome - thanks for the rep.

Pete