![]() ![]() ![]() 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. ![]() in A2 a date and in A3 this date plus 5 days as it should serve as a follow up due date and put some conditional formatting to it with colours if it is overdue to follow up.īut even though I inserted it now as a date (I hope) and put into A3 then =A2+5 it just shows "Value" but according to some other forum info it should add the to the days and would give me then in theory. But if I chose in the format the first option: should it then not show it the way it is displayed in that box? Then I entered the date as you said in this way: īut it shows now exactly how I entered it as. Then I chose those cells that are supposed to have a date format and chose the first option as with the asterisk in your screenshot. I marked all cells and chose format text. I looked up what the first ones look like under date and it is same as yours. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |