American Date to British Date Format

[josephteh]

How do I convert the dates to British dates - the dates are in Jul and Aug.

Thank you.

[nick.williams]

For your example this can work:

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

But it won't work for dates after the 12th of the month. Please show me what the format of your input data looks like for such dates.

[AliGW]

1. Select column A.
2. Data ribbon | Text to Columns.
3. Click Next > Next > choose DMY > Finish.

Then change the column formatting to SHORT DATE.

[josephteh]

Thanks Nick, but 1st July is not converted correctly.

[josephteh]

Thanks Ali, I need a formula-based approach.

[AliGW]

You did not say this in your opening post!

In B1 copied down:

=A1+0

Set formatting for column B to SHORT DATE.

[josephteh]

Apologies, A1. Thanks, but the formula converts Jul date correctly, but not Aug dates.

[HansDouwe]

The July date also already had a value associated with July.
The August dates have the values ​​of resp. 8 Jan, 8 Feb and 8 March.

These 3 dates can be converted with this formula: =DATE(YEAR(A2),DAY(A2),MONTH(A2))

[nick.williams]

You have changed your sample since my first response. You still haven't given an example of your input for dates after the 12th of the month.

In your latest file, cells A1 to A4 contain the date values of:

1st July
8th January
8th February
8th March

As you said the dates are all meant to be in July and August, I presume the first one is already correct. The other 3 can be converted to August using the formula I gave before. But as I said before this won't work for dates after the 12th, since it won't be formatted as the 8th day of the 13th month. I suspect it will be a text format and need some MID formulae to process, but unless you share the format then we can't help!

[josephteh]

Hi Nick, I changed my sample at 6.35pm and you must have read it before I changed it with your response at 6.37pm. Oh dear.. so sorry.. I should have opened a new thread.

[josephteh]

Thanks Hans. Yes, indeed Jul date is correct. But I need a formula to detect it and convert all the cells in A1 to A4 to proper dates with similar formulas.

[nick.williams]

The problem is, if some dates are already in UK format and others are in US format, how do we know which is which?

[HansDouwe]

If the day <= 12 there is no formula that can detect whether to swap month and day.

[josephteh]

Is it possible to detect based on the display formats? Noting UK dates show the time as 12:00:00 AM, while US dates show time as 0:00?

American to British Date Format.png

[MrShorty]

In the current sample file, the dates are stored as numbers, so you should be able to get any date/time format you want (within the limits of number formatting). Some date formats (like the "short date" format mentioned by AliGW) will refer to the computer's regional settings to decide what "short date" looks like on the current computer. When I open your file, I see that A1 is formatted as m/d/yyyy h:mm:ss AM/PM. The AM/PM is what controls the 12 or 24 hour clock. A2:A4 are formatted as m/d/yyyy h:mm. The absence of AM/PM specifies 24 hour clock.

If a date serial number is not formatted the way you want, you should be able to simply change the number format to what you want. If by "British Date" you mean "date/month/year with a 24 hour clock", then "d/m/yyyy 0:00:00" should work. If you want a 12 hour clock, add and AM/PM indicator.

Unless there is something more to the question that I am not understanding, it really should be as simple as applying the desired number format.

[josephteh]

Thanks MrShorty for your explanation. The problem is the file is shared by a third party and somehow the date format is not consistent. Within the same month, there could be 2 different formats. As mentioned in my post #14 above, the only way we could identify is by the date format: UK dates show the time as 12:00:00 AM, while US dates show time as 0:00. I was told the problem is somewhat related to the Mac. There are more than few thousand rows of data and we are unable to sort them and therefore unable to use different formulas to convert them.

[HansDouwe]

Please try in A2 and copy down:

Please Login or Register  to view this content.

[josephteh]

You nailed it, Hans!! Thank you!

[HansDouwe]

Good to hear it works. Thank you for the feedback and rep. .
I also learned how to test for format.

[AliGW]

Nice solution, Hans - one for the toolkit!

[nick.williams]

Still won?t work if there are US formatted dates after the 12th of the month?

[AliGW]

Here's a fix:

=IF(CELL("format",A1)="D4",A1,IFERROR(DATE(YEAR(A1),DAY(A1),MONTH(A1)),DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))))

[josephteh]

Thanks Nick for sporting the errors and thanks Ali for the fix!

[nick.williams]

@joseph - does that work? You've still not shared what the format actually is for dates after the 12th. If they look the same but are formatted as text then Ali's solution should work.

@Hans - nice solution. I didn't realise the formats had values you could reference e.g. "D4", I would have done something like =IF(CELL("format",A1)=CELL("format",$A$1), and so on.

[HansDouwe]

I didn't realise the formats had values

I didn't realise either until I tried =CELL("format",A1) .

[josephteh]

Yes, with Ali's fix, it works perfectly in my actual data.