Forum Discussion
Nested EDATE Function
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
=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.
2 Replies
=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.
- Thank you JoeUser2004. 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.
