1

In excel, is there a formula that can help highlight a reoccuring monthly due date and once that bill is paid for that month it changes back the color?

EXAMPLE

Vendor Due Jan Feb Mar Apr

Comcast 27th $100 $100

As shown in the example above, if I have a Comcast bill that is due the 27th of each month, how do I make it so the row highlights once that date arrive and the bill is not paid, but it does that for the entire year, meaning if there isn't a value in the cell that corresponds to the current month, it will highlight on the 27th and each month after on the 27th? So, it is march 27th and the March cell does not have a $100 value indicating that it is paid. and then again for April? etc

0

Perhaps pivot it the other way around, rather than having columns for each month?

1) A formula to convert a plain number like 27 into the date in the current month, that you can use as a basis for comparison (is today() > that date, and the amount paid column is empty?):

=DATEVALUE(MONTH(TODAY())&"/"&B2&"/"&YEAR(TODAY()))

2) Conditional formatting to show any rows where the criteria applies. This can be a total pain to manage, so I usually add a column with whatever logic is needed that returns "Y" or "N" and then apply the conditional formatting based on that.

That formula might look like:

=IF(E4<TODAY(),IF(D4<C4,"NO","YES"),"NOT DUE")

https://support.office.com/en-US/Article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f?ui=en-US&rs=en-US&ad=US

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.