Hi,
I am trying to write a formula that changes the cell colour if a delivery date has past. Is there a way of doing this? If no value is present in column B and the delivery date has past I would like the cell to turn red.
A B
Delivery Date Delivered Date
06/03/2012 05/03/2012
06/03/2012
Last edited by gtudor; 03-21-2012 at 09:20 AM.
Do you consider the 6/3/2012 to be passed today?
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Hi
Try this,in Conditional Formatting rules.
=B1<TODAY()
Hope to helps you.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
No, 07/03/2012 would be considered to be past / late.
Assuming the delivery dates start in A2 select from A2 down your range that you want to apply this to and in conditional format choose Formula Is and use: =AND(B2="",A2<TODAY()) and then set your format.
Dom
I cant seem to get this to work.
I have included the actual sheet to assist. I would like cells in column F to change to red if a delivery date has passed but not to change to red if there has been a confirmed delivery in column H.
For some reason I just cannot get this to work.Capture.JPG
Select F11:F15 (or as far as you want to go) and in conditional formatting use Formula Is: =AND(H11="",F11<TODAY()) and set your format.
Dom
Following on from this post, is there a way to change a cells colour if a delivery date is within 7 days from todays date?
One way is create a rule =(TEXT(A1-TODAY(),"D"))>=7
or
=(DAY(TODAY())-DAY(A1))>=7
Then pick a colour to format the cells.
Last edited by darknation144; 03-20-2012 at 10:09 AM.
Is this done within conditional formatting?Originally Posted by darknation144
Yes go to conditional and enter them as a new rule.
Home Tab > Conditional Formatting > New Rule > Use formula to determine which cells to format.
Not worked. Column V is where I would like to apply the formula. Any advice?
Capture.jpg
Sorry should be the other way round for your needs =(DAY(A1)-DAY(TODAY()))<=7
Last edited by darknation144; 03-20-2012 at 12:18 PM.
Still no luck. I am changing the cell location to V12 but cannot seem to get this one working
...A sample workbook, maybe??
Sample workbook attached.
I would like the cells in column J to change to amber if they are due within 7 days via conditoinal formatting?Sample 2.xlsx
...Take a look to the example..
It has worked up to a point. Blank cells also change to yellow, is there a work around to keep them white until a date is entered?
Yes there is..
Fantastic, thanks.
You are welcome
Time passed.xls
As you can see the formula highlights ones within 7 days of today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks