Hello, all. Long time reader, first time poster.
I started a new job last year as an accounting specialist and part of my scope of duties includes maintaining a very large Sales Report workbook covering 500+ clients and more than 15+ years of records. I built a formula which self populates text syntax due dates for Sales Reports so they would not affect the YTD Sales, Annualized Sales, Sales/Sq Ft & the # of Months reported columns at the end of each row. When a client submits sales reports, I key in the data over the previously shown text due date as an accounting format. I am now attempting to write a conditional formatting formula which will turn the cells yellow when the dates become expired, saving me loads of time and allowing me to see which clients need contacted without manually formatting each week.
My current Due Date formula is as follows:
="Due " & text(eomonth(X$10,0)+10, "mm/dd/yy")
Row 10 contains the individuals months, X10 being 8/1/2015. The +10 is for a client whose sales figures are due within 10 days of the close of the calendar month. As you can see, the formula in this example would produce an output of "Due 09/10/15". This has worked great for eliminating the need to update each due date from year-to-year or when adding new clients, editing terms on existing, etc. The problem arrives when I attempt to conditionally format the sheet to auto-highlight cells yellow when the date has expired.
I initially assumed a simple format rule would suffice.
FORMAT ONLY CELLS THAT CONTAIN:
CELL VALUE - LESS THAN - =TODAY()
That seems to fail because of the text syntax. When I use the above formatting formula on a hard-coded cell, it works fine. Unfortunately it also starts formatting some of my numeric sales figures.
There are several in upper management who view this book, so I have to keep things relatively clear, concise, and eliminate any causes for confusion - some have higher mental capacities than others, if you follow. I cannot use macros in this book. I basically just need a conditional formatting formula which will only highlight cells containing expiring dates while not affecting any of my numeric sales entries or my sum columns. Any ideas?
EDIT: I have a feeling I'm going to have to either change how my dates are entered or how my sum columns are formulated. Hoping I can avoid that, but open to any suggestions.
Thanks in advance,
CB9
I started a new job last year as an accounting specialist and part of my scope of duties includes maintaining a very large Sales Report workbook covering 500+ clients and more than 15+ years of records. I built a formula which self populates text syntax due dates for Sales Reports so they would not affect the YTD Sales, Annualized Sales, Sales/Sq Ft & the # of Months reported columns at the end of each row. When a client submits sales reports, I key in the data over the previously shown text due date as an accounting format. I am now attempting to write a conditional formatting formula which will turn the cells yellow when the dates become expired, saving me loads of time and allowing me to see which clients need contacted without manually formatting each week.
My current Due Date formula is as follows:
="Due " & text(eomonth(X$10,0)+10, "mm/dd/yy")
Row 10 contains the individuals months, X10 being 8/1/2015. The +10 is for a client whose sales figures are due within 10 days of the close of the calendar month. As you can see, the formula in this example would produce an output of "Due 09/10/15". This has worked great for eliminating the need to update each due date from year-to-year or when adding new clients, editing terms on existing, etc. The problem arrives when I attempt to conditionally format the sheet to auto-highlight cells yellow when the date has expired.
I initially assumed a simple format rule would suffice.
FORMAT ONLY CELLS THAT CONTAIN:
CELL VALUE - LESS THAN - =TODAY()
That seems to fail because of the text syntax. When I use the above formatting formula on a hard-coded cell, it works fine. Unfortunately it also starts formatting some of my numeric sales figures.
There are several in upper management who view this book, so I have to keep things relatively clear, concise, and eliminate any causes for confusion - some have higher mental capacities than others, if you follow. I cannot use macros in this book. I basically just need a conditional formatting formula which will only highlight cells containing expiring dates while not affecting any of my numeric sales entries or my sum columns. Any ideas?
EDIT: I have a feeling I'm going to have to either change how my dates are entered or how my sum columns are formulated. Hoping I can avoid that, but open to any suggestions.
Thanks in advance,
CB9
Last edited:
