Need help figuring out a formula please.
If "date entered" in cell exceedes todays date by 4 days, then format the cell background "Red".
Thanks so much for any help you can give.
Need help figuring out a formula please.
If "date entered" in cell exceedes todays date by 4 days, then format the cell background "Red".
Thanks so much for any help you can give.
Use a formula of
=A2>=TODYA()+4
where A2 is assumed as the first cell with CF
"Unplugged" <[email protected]> wrote
in message news:[email protected]...
>
> Need help figuring out a formula please.
>
> If "date entered" in cell exceedes todays date by 4 days, then format
> the cell background "Red".
>
> Thanks so much for any help you can give.
>
>
> --
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
http://www.excelforum.com/member.php...o&userid=36751
> View this thread: http://www.excelforum.com/showthread...hreadid=564685
>
Click on the cell (let's call it A1)
Use Format | Conditional Formatting
Formula: $A$1-TODAY()>4
Then set colour as needed
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Unplugged" <[email protected]> wrote
in message news:[email protected]...
>
> Need help figuring out a formula please.
>
> If "date entered" in cell exceedes todays date by 4 days, then format
> the cell background "Red".
>
> Thanks so much for any help you can give.
>
>
> --
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
> http://www.excelforum.com/member.php...o&userid=36751
> View this thread: http://www.excelforum.com/showthread...hreadid=564685
>
Should be TODAY() of course
"Bob Phillips" <[email protected]> wrote in message
news:uYRPVx#[email protected]...
> Use a formula of
>
> =A2>=TODYA()+4
>
> where A2 is assumed as the first cell with CF
>
> "Unplugged" <[email protected]> wrote
> in message news:[email protected]...
> >
> > Need help figuring out a formula please.
> >
> > If "date entered" in cell exceedes todays date by 4 days, then format
> > the cell background "Red".
> >
> > Thanks so much for any help you can give.
> >
> >
> > --
> > Unplugged
> > ------------------------------------------------------------------------
> > Unplugged's Profile:
> http://www.excelforum.com/member.php...o&userid=36751
> > View this thread:
http://www.excelforum.com/showthread...hreadid=564685
> >
>
>
Wow! Thanks so much for the quick reply.
Thought your formula works for the question I asked, I'm afraid I incorrectly asked my question.
My apologies. I will try again.
In Cell A1 I have entered a date. Eg. 01/01/06
What I want is for that cell to turn red when 4 days have passed the entered date.
Looking forward to your reply.
Cheers
Exactly 4 days?
=A2=TODAY()-4
or more than 4 days?
=A2<=TODAY()-4
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Unplugged" <[email protected]> wrote
in message news:[email protected]...
>
> Wow! Thanks so much for the quick reply.
> Thought your formula works for the question I asked, I'm afraid I
> incorrectly asked my question.
> My apologies. I will try again.
>
> In Cell A1 I have entered a date. Eg. 01/01/06
> What I want is for that cell to turn red when 4 days have passed the
> entered date.
>
> Looking forward to your reply.
> Cheers
>
>
> --
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
http://www.excelforum.com/member.php...o&userid=36751
> View this thread: http://www.excelforum.com/showthread...hreadid=564685
>
OK
I got it to work by using
=TODAY()>A1+4
The cell now turns 'red' when 4 days have passed, which is exactly what I wanted.
Now the only problem is...
When I copy that conditional formating to another cell, which is 'empty', it gets filled in with 'red'.
Is there a way to stop this from happening?
Thanks again.
You could use an "IF" statement in your conditional formula:
=IF(COUNT(A1)<>0,TODAY()>A1+4,0)
Then if there's something in the cell, it checks the date, otherwise, it
assumes you don't want to conditionally format it.
Michael
"Unplugged" <[email protected]> wrote in
message news:[email protected]...
>
> OK
> I got it to work by using
> =TODAY()>A1+4
> The cell now turns 'red' when 4 days have passed, which is exactly what
> I wanted.
>
> Now the only problem is...
> When I copy that conditional formating to another cell, which is
> 'empty', it gets filled in with 'red'.
> Is there a way to stop this from happening?
>
> Thanks again.
>
>
> --
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
> http://www.excelforum.com/member.php...o&userid=36751
> View this thread: http://www.excelforum.com/showthread...hreadid=564685
>
=AND(A1<>"",TODAY()>A1+4)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Unplugged" <[email protected]> wrote in
message news:[email protected]...
>
> OK
> I got it to work by using
> =TODAY()>A1+4
> The cell now turns 'red' when 4 days have passed, which is exactly what
> I wanted.
>
> Now the only problem is...
> When I copy that conditional formating to another cell, which is
> 'empty', it gets filled in with 'red'.
> Is there a way to stop this from happening?
>
> Thanks again.
>
>
> --
> Unplugged
> ------------------------------------------------------------------------
> Unplugged's Profile:
http://www.excelforum.com/member.php...o&userid=36751
> View this thread: http://www.excelforum.com/showthread...hreadid=564685
>
Thanks so much for all your help people.
It now works perfectly!
Really nice to know there are people out there who are so generous with their knowledge and time.
Best wishes, and thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks