Nov 21 2018 01:30 AM
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.
Nov 21 2018 02:30 AM
SolutionIt 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.
Nov 21 2018 03:10 AM
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
Nov 21 2018 03:27 AM
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
Nov 21 2018 02:30 AM
SolutionIt 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.