Forum Discussion
Date Formatting
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.
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