Forum Discussion
show a due date using data from multiple columns
I have a spreadsheet in which I have multiple date values - a client's birthday, follow up visit date 1, date 2, date 3. for each date column I have conditional formatting set up to mark the cell yellow if it's within a month from today, and red if it's next week. However, I'd like to add a column to the very beginning of the spreadsheet that will simply alert me if any relevant date for that client is coming up soon (i.e. the birthday is within a month or their follow up visit 2 is within a month). I've been trying to use IF And/or formulas but it either it's not functioning properly or it tells me there's an error. (examples of things I've tried below).
I want it to show "Due" if a date from any of those columns is within the next 30 days and "Updated" if not (but don't want it to show Due if the dates have passed).
=IF(OR(30>M16-TODAY()>0,30>N16-TODAY()>0,30>T16-TODAY()>0,30>V16-TODAY()>0,30>X16-TODAY()>0,30>Z16-TODAY()>0),"Due","Up to Date")
=IF(AND(OR(M11-TODAY()>0,N11-TODAY()>0,T11-TODAY()>0,V11-TODAY()>0,X11-TODAY()>0,Z11-TODAY()>0), OR(M11-TODAY()<30,N11-TODAY()<30,T11-TODAY()<30,V11-TODAY()<30,X11-TODAY()<30,Z11-TODAY()<30)),"Due","Up to Date")
=IF(OR(AND(M16-TODAY()>0, M16-TODAY()<30), AND(N16-TODAY()>0, N16-TODAY()<30), AND(T16-TODAY()>0, T16-TODAY()<30), AND(V16-TODAY()>0, V16-TODAY()<30), AND(X16-TODAY()>0, X16-TODAY()<30), AND(Z16-TODAY()>0, Z16-TODAY()<30)), "Due", "Up to Date")