Nested EDATE Function

[Bailey317]

Excel Microsoft 365/Windows 10/Laptop

I am having trouble figuring out how to write the formula using a nested EDATE function. I am creating a spreadsheet of food expirations and some of the products use Julian dates and some use best buy dates. For the Julian dated ones I am converting the Julian Date to a calendar date using the below formula:

=IF(C5="","",(DATE(IF(0+(LEFT(C5,2))<30,2000,1900)+LEFT(C5,2),1,MID(C5,3,3))))

Is it possible to nest an EDATE function within the above formula to add the shelf life to the result of the Julian date being converted?

Currently I have the following EDATE formula in a separate column but I am trying to avoid that if possible and combine the 2 formulas:

=IF(D5="","",(EDATE(D5,B5)))

The expiration date column will have data imputed for the best buy dated products and excel does not allow the ability to lock the formula only in a cell.

Any help is greatly appreciated.  I haven't been able to figure out how to structure the combined formulas.  I am new to using formulas that venture off the path of easy into more complex.

Thanks

[#2 · Accepted solution]

@Bailey317 

=IF(C5="", "",

EDATE(DATE(IF(--LEFT(C5,2)<30, 2000, 1900)+LEFT(C5,2), 1, RIGHT(C5,3)), B5))

If C5 might contain only 4 digits for dates less than 2010, replace C5 with TEXT(C5,"00000") .

I don't know how you calculated E7.

[#3]

Thank you @Joe User. You are awesome! That worked. I was getting myself so confused on how to nest that EDATE. I really appreciate you taking the time to help me with that.