Changing US date format to UK format - using a formula

Hi there,

I am using the XLTools calendar on the attached sheet, I can't get service pack 6 so am unable to use the Microsoft one.

The date format in this calendar is US, either mm/dd/yyyy or mm/d/yyyy, depending on the day of the month.

I am trying to use a formula to alter this to the UK format of dd/mm/yyyy (and/or d/mm/yyyy if necessary). It is not possible to format the cell as normal, I have tried formulas I've found online but to no avail. Some work for any date of 1st-12th of the month but not 13th onwards, or vice versa.

If you look at the sheet, cell Q18 is where the date result from the calendar will be, I need cell C8 to show the date in UK format.

I would appreciate any help/advice you could give.

Many thanks in advance.

Matt

Sorry, I didn't explain too well. You use the calendar to select your date, the date will then show in whichever cell is selected (I will click cell Q18 before I select a date on the calendar).

Q18 will then show the US format date. There needs to be a formula in C8 which pulls the date from Q18 and formats it to the UK date.

The settings on my PC are UK, this is a known issue with the XL Tools calendar.

I hope that makes sense.

Thanks

Matt

Hi Tony,

Unfortunately this does not work, this is one of the one's I found online. If the date is the 13th of the month or later, it works. If the date is the 1st-12th of the month it doesn't.

Glenn,

I inserted the calendar from the store on the insert menu, see pic, if that helps?

Appreciate both of you helping.

Matt

Originally Posted by Tony Valko

On a side note...

There should be an international standard for date formats.

d/m/y or m/d/y, one or the other!

I agree completely, would make life so much easier!

Originally Posted by Glenn Kennedy

If you format Q18 as a number, do you get 42669? If do, what do you see in C8 if you enter (in c8):

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

and format as a date?

Have tried this Glenn, doesn't appear to work.

it returns the date in the same format 10/12/2016

I have to leave the office now and won't have access to a PC until 9am tomorrow, thanks for your help guys, if you do reply between now and then I'll respond as soon as I get in.

Appreciate you taking the time to assist me.

Thanks

Matt

Originally Posted by MrShorty

I note that Q18 is preformatted as text ("@"), and that =ISTEXT(Q18) returns TRUE. It seems to me that however the date is getting entered into Q18, someone has put some effort to make sure that Q18 is a text string and not a real date value. Perhaps the first step is to convert the date as text to a real date serial number (perhaps using the DATEVALUE() function). Once the date is converted to a serial number, you can then format it however you want using built in or custom date format codes.

If you want to store C8 as a text string, then you can simply add the DATEVALUE() function to Tony Valko's formula in post #13. =TEXT(DATEVALUE(Q18),...)

Unfortunately this does not work for me, any date form 1st - 12th still shows in US format and any date from 13th onwards returns #VALUE!

Originally Posted by Tony Valko

Maybe this...

=DAY(Q18)&"/"&MONTH(Q18)&"/"&YEAR(Q18)

That'll return a TEXT string in the format d/m/yyyy.

If you want d/mm/yyyy...

=DAY(Q18)&"/"&TEXT(MONTH(Q18),"00")&"/"&YEAR(Q18)

If you want dd/mm/yyyy...

=TEXT(DAY(Q18),"00")&"/"&TEXT(MONTH(Q18),"00")&"/"&YEAR(Q18)

The same for these I'm afraid, 1st-12th shows as US format, the rest return #VALUE!

Try

=DATEVALUE(SUBSTITUTE(MID(Q18,FIND("/",Q18)+1,7),"/","/"&LEFT(Q18,FIND("/",Q18))))

Or enter this code into the worksheet module, (right click the sheet tab in excel, then View Code') and save your workbook with a .xlsm file extension, then select C8 before using the date picker. The code eliminates the need for the extra cell (Q18).

Please Login or Register  to view this content.

Try this formula:

=IF(ISNUMBER(Q18),DATE(YEAR(Q18),DAY(Q18),MONTH(Q18)),DATE(RIGHT(Q18,4),LEFT(Q18,FIND("/",Q18)-1),SUBSTITUTE(MID(Q18,FIND("/",Q18)+1,2),"/","")))

Format as a date in the style you require.

Hope this helps.

Pete

Originally Posted by jason.b75

Try

=DATEVALUE(SUBSTITUTE(MID(Q18,FIND("/",Q18)+1,7),"/","/"&LEFT(Q18,FIND("/",Q18))))

Or enter this code into the worksheet module, (right click the sheet tab in excel, then View Code') and save your workbook with a .xlsm file extension, then select C8 before using the date picker. The code eliminates the need for the extra cell (Q18).

Please Login or Register  to view this content.

Thanks Jason! This works a treat, I've gone with the first option, I prefer to avoid code where possible.
Really appreciate your help!

Thank you everyone for your help, much appreciated.

Originally Posted by Pete_UK

Try this formula:

=IF(ISNUMBER(Q18),DATE(YEAR(Q18),DAY(Q18),MONTH(Q18)),DATE(RIGHT(Q18,4),LEFT(Q18,FIND("/",Q18)-1),SUBSTITUTE(MID(Q18,FIND("/",Q18)+1,2),"/","")))

Format as a date in the style you require.

Hope this helps.

Pete

Thanks Pete, didn't get round to trying this one as Jason's worked, but appreciate you taking the time to help.