I need help getting one NON numerical cell variable to change when a numerical cell variable changes but with specific conditions.
Issue:
Cell A = original operation date
Cell B= operation month
Cell C= follow up due this date
Cell D= days remaining till follow up due date (=cellC-TODAY())
Cell E= Timepoint i.e. 6 month, Year 1, Year 2, Year 3, etc. which is based of cell A i.e. Cell A= 2/8/13, Cell C= 9/5/18, Cell E= Year 5 because cell c is 5 years from cell A
Cell F= 6 month followup due date (IF Cell A was before or on Dec. 31, 2015=cellA+90, IF Cell A was on Jan 2016 or after =cellA+300)
Cell G= Year 1 follow up due date (=cellA+365+180+30)
The date in Cell C calculates the value for the number in Cell D, and Cell E reflects the non numerical value (i.e 6 month, Year 1, Year 2, etc).
If I change the date in Cell C, then the value (# of days left) in Cell D changes, but I need Cell E to change accordingly with conditions.
For example:
Cell C = 9/6/2017
Cell D = 1 (days left)
Cell E= Year 7
If I change:
Cell C = 9/6/2018, then
Cell D= 366 (days left)
but I need Cell E to change to = Year 8 WITHOUT me manually having to do it. I think I found the formula for that ="Year" & DATEDIF(cellA,cellD,"Y")
AND it's different if it's a 6 month time point
Because if change the entire column for Cell E to reflect the above equation, anything less than their respective date for the 1 year follow up doesn't show. It comes out as Year0. Either way, IF it is less than their 1 year follow up, I dont want exact months to show I just need it to say "6 month" automatically. I think I figured out the formula specifically for that situation =IF(AND(DATEDIF(cellA,cellD,"Y")<cellF),"6 month","")
But I dont know how to combine the two conditional equations into just one. Basically if it's less than their 1 year follow up date it needs to say 6 months, if not then it needs to change every time I change the date in cell C
Solved by T. J. in 29 mins