Date Formatting

New Contributor



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

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



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 :) 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies