Subtract a number of days from a date included in a string text, resulting another date

[vladimiratanasiu]

Hello!

I have a table with data, regarding products of a factory, as shown in the link Date.xlsx. The column A has information about every lot of products, formatted as text. Some cells have only the date, other ones have included also the number of the lot, delimitated by the "/" symbol. The column B has cells with number of days to be subtracted from the cells of column A. In the column C, I need the number of days to be subtracted from the date of string text, resulting the final date shown as example in cells C2, C3, C8 etc. What's the right formula, to get the necessary results? P.S. Even if some dates are formatted like 17/04/2022, ignore them and consider the final formula taking into account only date formatted as 17.04.2022

Thank you!

 

[ian9999]

Hi Vladimiratansiu

Could you please check if the below formula works?

=IF(MID(A2,11,1)="/",TEXT(SUBSTITUTE(LEFT(A2,10),".","/")+0-B2,"DD/MM/YYYY")&RIGHT(A2,2),SUBSTITUTE(LEFT(A2,10),".","/")+0-B2)

Regards

Ian

 

[vladimiratanasiu]

Hi Vladimiratansiu

Could you please check if the below formula works?

=IF(MID(A2,11,1)="/",TEXT(SUBSTITUTE(LEFT(A2,10),".","/")+0-B2,"DD/MM/YYYY")&RIGHT(A2,2),SUBSTITUTE(LEFT(A2,10),".","/")+0-B2)

Regards

Ian

Thank you for the quick response! In the cells C1:C7, C14:C16 and C25, the content is very good. The other results include also number of the lots (/1, /2, /3 etc). Could you adjust the formula in order do remove that sequence and keep only the date? Thank you!

 

[ian9999]

Thank you for the quick response! In the cells C1:C7, C14:C16 and C25, the content is very good. The other results include also number of the lots (/1, /2, /3 etc). Could you adjust the formula in order do remove that sequence and keep only the date? Thank you!

Try this formula

=SUBSTITUTE(LEFT(A2,10),".","/")+0-B2

Regards

Ian

 

[vladimiratanasiu]

Try this formula

=SUBSTITUTE(LEFT(A2,10),".","/")+0-B2

Regards

Ian

Thank you very much, you are brilliant!