Thanks u/Pyromanga!!Īcronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: Fewer Letters

If you have dates that go back into the 1900-series, we'll need some kind of way to identify which 2-digit dates are to be considered 1900-series and which should be 2000-series.ĮDIT: Fixed formula.

NOTE: For the ones with a 2 digit date, that formula assumes they are all in the year 2000-series (no 1900's dates). So now, after confirming what date Excel is giving you (from your D2+1 formula), if it is giving you the correct date, and all we need to do is re-configure those other ones - when Excel barks the #VALUE error - try this and see if it behaves as intended: =IFERROR(D2+1,(DATE(RIGHT('20'&FILTERXML(''&SUBSTITUTE(FILTERXML(''&SUBSTITUTE(D2,'/','')&'','//d'),' ','')&'','//d'),4),FILTERXML(''&SUBSTITUTE(D2,'/','')&'','//d'),FILTERXML(''&SUBSTITUTE(D2,'/','')&'','//d'))+FILTERXML(''&SUBSTITUTE(D2,' ','')&'','//d'))+1) You can press Ctrl z to put the format back the way it was now.

From your examples, on the 2 that are NOT giving #VALUE errors, is Excel giving you the correct answer? Select those 2 cells and format them (temporarily) to General.

Format date cells in excel not working