SOLVED
Home

Date Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-289703%22%20slang%3D%22en-US%22%3EDate%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289703%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20Could%20anyone%20help%20me%20changing%20a%20date%20from%20the%20format%20Nov%2016%2C%202018%20to%20the%20format%2016%2F11%2F2018.%20Right%20clicking%20on%20the%20cell%20and%20using%20format%20cells%20doesn't%20work%20as%20there%20is%20no%20option%20for%20this.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20238px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60415i6394165A7F501A05%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-289703%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289773%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289773%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20really%20appreciate%20the%20help%20%3A)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289770%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289770%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20forgot%20about%20that.%20Please%20change%20formula%20on%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR((MID(%5B%40Date%5D%2C5%2CSEARCH(%22%2C%22%2C%5B%40Date%5D)-5)%20%26amp%3B%20%22-%22%20%26amp%3B%20LEFT(%5B%40Date%5D%2C3)%20%26amp%3B%20%22-%22%20%26amp%3B%20RIGHT(%5B%40Date%5D%2C4))%2B0%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EIFERROR%20is%20for%20empty%20cells.%3C%2FP%3E%0A%3CP%3Eand%20in%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289761%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289761%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20could%20you%20have%20a%20look%20at%20the%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20the%20dates%20are%20pasted%20from%20a%20web%20software%20the%20are%20missing%20a%20digit%20so%20the%20formula%20wont%20reformat%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ee.g.%209%20Nov%2C%202018%20wont%20change%20to%2009%2F11%2F2018%20due%20to%20not%20being%2009%20Nov%2C%202018%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20change%20the%20num_chars%20to%20the%20correct%20number%20for%20the%209th%20this%20then%20makes%20the%20formula%20not%20work%20for%20anything%20above%20the%2010th%20as%20it%20will%20make%20it%20then%20become%20the%2001%2F11%2F2018%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20work%20around%20this%20without%20it%20being%20manually%20changed%20every%20time%20as%20this%20is%20a%20sheet%20that%20needs%20to%20be%20simple%20data%20entry%20for%20a%20sales%20team.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289750%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289750%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289747%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289747%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20works%20perfectly%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289737%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289737%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20looks%20like%20your%20dates%20are%20in%20text%20format%2C%20you%20can't%20covert%20them%20to%20dates%20just%20by%20applying%20date%20format.%20You%20may%20convert%20them%20to%20dates%20in%20helper%20column%20by%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3D(MID(A1%2C5%2C2)%20%26amp%3B%20%22-%22%20%26amp%3B%20LEFT(A1%2C3)%20%26amp%3B%20%22-%22%20%26amp%3B%20RIGHT(A1%2C4))%2B0%3C%2FPRE%3E%0A%3CP%3Eand%20after%20that%20apply%20desired%20date%20format%20to%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Cam-17_Kan
New 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
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 :) 

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