Date substraction.
hello everyone, can anyone help me to get previous day date from the selected date. Including this formula:
=IF(LEN(B2)=5,"0"&B2,B2)
I am getting date in cell B2 like 041022 and I want 031022 in cell A2.
CodePudding user response：
You may try using this formula, assumption is you are taking years after 2000
• Formula used in cell A2
=TEXT(TEXT(REPLACE(IF(LEN(B2)=5,0&B2,B2),5,0,20),"00\/00\/0000"),"dd-mm-yyyy")-1
Or, one can use a 
• Formula used in cell A9
=LET(_number,IF(LEN(B9)=5,0&B9,B9),
DATE(RIGHT(_number,2),MID(_number,3,2),LEFT(_number,2))-1)
Here is another alternative, if one have access to TEXTJOIN()
• Formula used in cell A19
=TEXTJOIN("/",,MID(IF(LEN(B19)=5,0&B19,B19),{3,1,5},{2,2,2}))-1
Note: Since dates are stored as numbers in Excel, hence it will return as numbers, need to format as Excel Dates by either pressing CTRL SHIFT 3 or press CTRL 1 from Format Cells dialog click Number Tab and select Date press Ok.