Differences between different formats

[Peter Quill]

How to find the differences in dates for the attached data?

 

[cheekybuddha]

You will have trouble because it doesn't seem as if the formats are consistent within columns.

Eg A8 and A9 in the CRIG column are dd/mm/yyyy whereas A1 is mm/dd/yyyy.

Who know which way round the ambiguous dates are (eg A10 and A11 in CRIG column)!

 

[Peter Quill]

Yes, David Marten. So, how can I convert them into the same format and subtract them afterward? I really appreciate any help you can provide.

 

[arnelgp]

you can use a VBA function?

 

[Gasman]

@arnelgp The dates are all over the place?
How is anyone meant to know if C14 or C15 is 1st July or 7th January?

I was going to suggest looking for one of your functions, but even you need to know the actual date for each parameter?

 

[Peter Quill]

Dear ARNELGP,
This worked perfectly. Thanks for the support. This is great!

 

[cheekybuddha]

So, how can I convert them into the same format and subtract them afterward

It depends how many you have to deal with.

I haven't looked at Arnel's suggestion, but I would first choose which format you wish to use for all dates (mm/dd/yyyy or dd/mm/yyyy). Perhaps, he does this in his example.

Then, in columns D and E I would create a formula that formats the unambiguous dates in your format of choice, and writes 'CHECK' for the ambiguous dates so that you can verify them manually.

 

[arnelgp]

the Column SSINIT is Text, using CDate() will convert it to date (in your Locale), so you don't
worry whether the first number in the text is a Day or a Month.
Your Locale will determine that using CDate().

 

[cheekybuddha]

That assumes that the ambiguous dates have been entered in the date format of your locale.

A bit of a dangerous assumption IMHO!

 

[Gasman]

I do like B17, 32/13/1991