SOLVED

Date Formatting

Copper Contributor

Hi

 

Please Could anyone help me changing a date from the format Nov 16, 2018 to the format 16/11/2018. Right clicking on the cell and using format cells doesn't work as there is no option for this.Capture.PNG

6 Replies
best response confirmed by Cam-17_Kan (Copper Contributor)
Solution

It looks like your dates are in text format, you can't covert them to dates just by applying date format. You may convert them to dates in helper column by formula like

=(MID(A1,5,2) & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))+0

and after that apply desired date format to it.

 

Thank you so much works perfectly :)

You are welcome

Hi

 

Please could you have a look at the attached.

 

Where the dates are pasted from a web software the are missing a digit so the formula wont reformat them.

 

e.g. 9 Nov, 2018 wont change to 09/11/2018 due to not being 09 Nov, 2018

 

If i change the num_chars to the correct number for the 9th this then makes the formula not work for anything above the 10th as it will make it then become the 01/11/2018

 

Is there any way to work around this without it being manually changed every time as this is a sheet that needs to be simple data entry for a sales team.

 

Thank you in advance

 

Sorry, forgot about that. Please change formula on

=IFERROR((MID([@Date],5,SEARCH(",",[@Date])-5) & "-" & LEFT([@Date],3) & "-" & RIGHT([@Date],4))+0,"")

IFERROR is for empty cells.

and in attached

Thank you so much really appreciate the help :) 

1 best response

Accepted Solutions
best response confirmed by Cam-17_Kan (Copper Contributor)
Solution

It looks like your dates are in text format, you can't covert them to dates just by applying date format. You may convert them to dates in helper column by formula like

=(MID(A1,5,2) & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))+0

and after that apply desired date format to it.

 

View solution in original post