Date formats

[wildturkey]

Not sure where to start on this

I have an external data source in excel with a list of dates, I do not believe that these dates are being recognised as dates and have used =DATE(RIGHT(B968,4),MID(B968,4,2),LEFT(B968,2)) on the cells which appear as 30/06/2019 and this gives me what I'm hoping is a list of dates in my new column A.

When I sort column A everything looks fine until half way down the list when I start getting dates such as

12/09/3473, 13/10/3474 etc and these are pointing at cells that read 03/01/2019 and 04/01/2019 respectively. If I format as numbers the original cells I get 43468 and 43469 which seams to suggest that they are indeed dates, and if I format the new dates in column A I get 574783 and 575179.

On the initial part of the dates list, if I format the original dates as numbers nothing changes, but I do get 43480 or similar if I format as numbers the new dates in column A.

My problem though is definitely the second hald where =DATE(RIGHT(B968,4),MID(B968,4,2),LEFT(B968,2)) is failing, despite the appearance of the values in column B all appearing as dates...

Many thanks

 

[Fluff]

Try
=IF(ISNUMBER(B968),B968,DATE(RIGHT(B968,4),MID(B968,4,2),LEFT(B968,2)))

Alternatively you can just use "Text to columns" on col B to convert them to real dates.

 

[wildturkey]

Cell A1 contains my original formula and shows 03/01/2019 referencing cell B1 which contains 03/01/2019, when I use text to columns cell A1 shows 12/09/3473 and cell B1 03/01/2019 although A1 becomes superfluous..

However, when I use your formula all in column A look and work fine - coincidentally if I use text to columns and your formula everything also works fine.

Many thanks!!

 

[Fluff]

What you need to be careful of is why some of the dates are being recognised & some are not.

03/01/2019
will be recognised as a date regardless of whether you use UK, or American style date format.
The problem is US style will read that as 1st March, whilst UK style will see 3rd June & the question being, which is correct?
While 30/06/2019 is OK in the UK, but not in US & vice versa with 06/30/2019.

It maybe that while some of the dates are being seen as such, are they correct?

You will get values such as 12/09/3473 if the original value is a date.

 

[wildturkey]

It's bizarre as 06/06/2019 is fine but 03/01/2019 gives me 12/09/3473. Happy with the solution though

 

[Fluff]

You're welcome & thanks for the feedback