Numbers - Date formulas - Due date

Question

Smäck Author

Level 1

4 points

Numbers - Date formulas - Due date

Hi,

I want a line or text to turn red (background color or text color) if a date is due.

Column Q = Invoice date

Column S = Paying terms

Column U = Due date (formula from Q+S)

Column Y = Yes if paid or unfilled if not paid

Column W = Today's date

I want a specific row to change color if due date has passed accordingly to today's date and if I have not written Yes in column Y.

So, in my brain the formula should look similar to:

If U has passed W make text on this row into red color and bold, unless column Y says Yes. If column Y is saying Yes, don't do anything to this row.

This formula has to work the other way around as well, in case it has turned red and I then type in Yes in column Y, the row should go back to normal state, which is font in black and normal.

Anyone who can support me with this one?

Posted on Dec 29, 2019 5:12 AM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

Dec 29, 2019 7:30 AM in response to Smäck

You can set up like this:

select cell S2, then type (or copy and paste from here) the formula:

=Q2+R2

shorthand for this is:

S2=Q2+R2

U2=IF(T2="YES", "", IF(S2>=TODAY(), "YES", ""))

fill fill down, select cell S2, copy,

select cell S2 thru the end of column S or as needed, paste

repeat for U2

as for highlighting a row based on the content of a cell... it is possible but, early worth the effort. I suggest using the categories feature to help you see outstanding balances:

Reply

Numbers - Date formulas - Due date

Similar questions