Formula for Fiscal Year Conditional Formatting
I'm looking for a conditional formatting formula to keep track of follow-on
contract due dates. I've found many helpful formulas for calculating due dates from a starting date...but, not working backwards from an end date. Any ideas? Some contracts are on a fiscal year schedule (30 Sep) and some are off-cycle (any date.) How do I set up a formula that will color a cell red one month before contract expiration and turn the cell yellow 180 days before contract expiration. The formula must take into account that the cell includes the expiration date. Thanks in advance! |
Formula for Fiscal Year Conditional Formatting
Hi,
you can use conditional formatting on cells with expiration date, by using the formula (for 30 days assuming that expiration date is in cell a2 comparing to current date): =SUM(A2-NOW())<31 You can add another condition to conditional formatting for 180 days. Regards, Nika Lampe "eppersbl" je napisal: I'm looking for a conditional formatting formula to keep track of follow-on contract due dates. I've found many helpful formulas for calculating due dates from a starting date...but, not working backwards from an end date. Any ideas? Some contracts are on a fiscal year schedule (30 Sep) and some are off-cycle (any date.) How do I set up a formula that will color a cell red one month before contract expiration and turn the cell yellow 180 days before contract expiration. The formula must take into account that the cell includes the expiration date. Thanks in advance! |
Formula for Fiscal Year Conditional Formatting
Thanks! I definitely think this might work!
"Nika Lampe" wrote: Hi, you can use conditional formatting on cells with expiration date, by using the formula (for 30 days assuming that expiration date is in cell a2 comparing to current date): =SUM(A2-NOW())<31 You can add another condition to conditional formatting for 180 days. Regards, Nika Lampe "eppersbl" je napisal: I'm looking for a conditional formatting formula to keep track of follow-on contract due dates. I've found many helpful formulas for calculating due dates from a starting date...but, not working backwards from an end date. Any ideas? Some contracts are on a fiscal year schedule (30 Sep) and some are off-cycle (any date.) How do I set up a formula that will color a cell red one month before contract expiration and turn the cell yellow 180 days before contract expiration. The formula must take into account that the cell includes the expiration date. Thanks in advance! |
All times are GMT +1. The time now is 08:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com