Conditional Formatting Expired Dates Embedded in TEXT Formula

Cavball9

New Member
Joined
Oct 20, 2015
Messages
4
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
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You should still be able to use Conditional Formatting, you just need to use a conditional format formula that can extract the date from your string. This tested well for me for cells containing text such as "Due 10/19/15"

=(RIGHT(A1,8)+0) < TODAY()

[Edit] remove the spaces before and after the < symbol. The board was truncating my formula.[/Edit]
Where A1 represents the upper left cell of the range you want to format.

If it doesn't work at first double check that excel did not automatically put in quotation marks (if that happens just remove them).
 
Upvote 0
Or refer to the cell that actually contains the original date (X$10)

Now I don't believe you can use the eomonth function in conditional formatting.
So I'd suggest a helper column (both for the original formula, and for conditional formatting)
So if the original Date is in X10
Then say Y10, put =EOMONTH(X$10,0)+10
Then your due date formula is
=-"Due " & TEXT(Y$10,"mm/dd/yy")

Then conditional formatting is
Use Formula
=$Y10 < TODAY()<today()< html=""></today()<>
 
Upvote 0
You should still be able to use Conditional Formatting, you just need to use a conditional format formula that can extract the date from your string. This tested well for me for cells containing text such as "Due 10/19/15"

=(RIGHT(A1,8)+0) < TODAY()

[Edit] remove the spaces before and after the < symbol. The board was truncating my formula.[/Edit]
Where A1 represents the upper left cell of the range you want to format.

If it doesn't work at first double check that excel did not automatically put in quotation marks (if that happens just remove them).

Nailed it! You have no idea how much time you've just saved me! Thank you, thank you, thank you!

If you don't mind - what is the purpose of the +0 portion of the formatting function? I noticed it produces the opposite TRUE/FALSE output when omitted.
 
Last edited:
Upvote 0
If you don't mind - what is the purpose of the +0 portion of the formatting function? I noticed it produces the opposite TRUE/FALSE output when omitted.

The results of RIGHT(A1,8) is still technically text, I believe since RIGHT is a text function. The +0 forces excel to evaluate it as a numeric/date value, something that can be compared to Today() properly.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top