Using Concatenate & Ifs functions to convert US dates into British Dates

[Stephen Buttle]

Hi there,

I have worked out a way of converting US dates into British ones using delimited and concatenate function to restore leading zeros lost after date data seperation. The next step is to nest these concatenate functions into 1 formula so it can simply be dragged down the page.

I've programmed concatenate with 4 conditional values;

1) if month <10 then return date by concatenating day, month & year by inserting these digits.
2) If day <10 then return date by concatenating day, month & year by inserting those digits.
3) if month & day are both <10 then return date by concatenating day, month & year by inserting other digits.
4) if month & day are both=>10 then concatenate cells by day, month & year.

How do I use IFS or IF functions to nest these conditions into 1 formula?

Sample sheet is attached. Any help/suggestons would be appreciated.

[JohnTopley]

Try

=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

format as "dd-mm-yyyy"

[swaatacba]

@Stephen

I think you are trying to do this for yourself rather than using Excel's quite sophisticated date management functions.

You have several different concatenation formulae (4 - I think) in your column that converts US dates to UK ones. It would be good if you could match the formulae up to the descriptions in your first post.

We might then have some chance of identifying whether there is a clear cascading order of IF statements.

Stephen

[Fluff13]

Another option

Please Login or Register  to view this content.

and format as date.