How to convert a date such as 18DEC26 into an Excel usable date 18/12/26

Question

Jeffrey Frankel 0

I want to convert a date when written as text 18DEC26 into an excel usable date 18/12/26

Rory Archibald 19,230 Reputation points Volunteer Moderator

2026-11-05T11:23:33.5366667+00:00

Depending on your regional settings, you might simply be able to use =A2+0 where A2 contains 18DEC26. Works for the UK at least.
Jeffrey Frankel 0 Reputation points

2026-11-05T12:08:52.2833333+00:00

Yes this works for me, thank you and so simple

1 answer

Rory Archibald 19,230 Reputation points Volunteer Moderator

2026-11-05T11:23:33.5366667+00:00

Depending on your regional settings, you might simply be able to use =A2+0 where A2 contains 18DEC26. Works for the UK at least.
Jeffrey Frankel 0 Reputation points

2026-11-05T12:08:52.2833333+00:00

Yes this works for me, thank you and so simple

Answer 1

DaveM121 946.1K Independent Advisor

The formula provided by the Ai is far too complex.

If that data is in cell A1, then use this formula in another cell

=DATEVALUE(A1)

Then right click that cell and choose Format Cells and set the cell format to General - dd\mm\yyyy

Jeffrey Frankel 0 Reputation points

2026-11-05T12:10:34.1866667+00:00

Yes it works for me but not as simple as the one above (=A2+0)
DaveM121 946.1K Reputation points Independent Advisor

2026-11-05T12:11:37.55+00:00

That is great news, I am glad you have found a solution that works for you.