Conditional formating is applied based on date in two cells
Solved/Closed
Capricorn56
Posts
1
Registration date
Wednesday July 23, 2014
Status
Member
Last seen
July 23, 2014
-
Jul 23, 2014 at 03:54 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Mar 16, 2015 at 01:04 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Mar 16, 2015 at 01:04 PM
Related:
- Based on the values in cells b77 b81
- Based on the values in cells b77 b88 ✓ - Excel Forum
- Based on the values in cells b77 b81 what function can automatically return the value in cell c77 ✓ - Excel Forum
- Looking For a Value in a Cell - Excel Forum
- Looking up and adding the values fixing different criteria - Excel Forum
- Conditional Formatting range of date cells based on another cell ✓ - Excel Forum
4 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 27, 2022
555
Jul 24, 2014 at 11:29 AM
Jul 24, 2014 at 11:29 AM
Hi Capricorn,
What you are looking for is the AND function, which lets you enter more conditions.
=AND($E2=TODAY(),$H2="")
or more specifically:
=AND($E2=TODAY(),$H2<>"Eligible",$H2<>"Ineligible",$H2<>"Early Intervention",$H2<>"Withdrawn")
Best regards,
Trowa
What you are looking for is the AND function, which lets you enter more conditions.
=AND($E2=TODAY(),$H2="")
or more specifically:
=AND($E2=TODAY(),$H2<>"Eligible",$H2<>"Ineligible",$H2<>"Early Intervention",$H2<>"Withdrawn")
Best regards,
Trowa
Hi Kioskea
Thank you for your reply and sorry it has taken me so long to respond. I've had some other technical issues. I have tried the formula and it doesn't work. Do you have any other suggestions I could try. Thanking you for your time.
Kind Regards
Thank you for your reply and sorry it has taken me so long to respond. I've had some other technical issues. I have tried the formula and it doesn't work. Do you have any other suggestions I could try. Thanking you for your time.
Kind Regards
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 27, 2022
555
Aug 11, 2014 at 11:27 AM
Aug 11, 2014 at 11:27 AM
Hi Capricorn,
Actually the formula works.
I put today's date in E1:E3, Eligible in H1 and Withdrawn in H3.
As a result only the date in E2 got formatted as seen in the below pic:
It is a shorter version with less conditions but it does the trick for the example shown.
It is a Dutch version where EN means AND, VANDAAG means TODAY and I have to use ; as separator instead of ,.
If you can't figure out what is happening then consider posting your workbook using a file sharing site like www.speedyshare.com or ge.tt and post back the download link. Careful with personal information.
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Actually the formula works.
I put today's date in E1:E3, Eligible in H1 and Withdrawn in H3.
As a result only the date in E2 got formatted as seen in the below pic:
It is a shorter version with less conditions but it does the trick for the example shown.
It is a Dutch version where EN means AND, VANDAAG means TODAY and I have to use ; as separator instead of ,.
If you can't figure out what is happening then consider posting your workbook using a file sharing site like www.speedyshare.com or ge.tt and post back the download link. Careful with personal information.
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 27, 2022
555
Mar 16, 2015 at 01:04 PM
Mar 16, 2015 at 01:04 PM
Hi Keno,
Here is your requested formula:
=AND(A1<DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())),A1>DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
You might want to change the < and > symbols into <= and >= depending on the results you are after.
Best regards,
Trowa
Here is your requested formula:
=AND(A1<DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())),A1>DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
You might want to change the < and > symbols into <= and >= depending on the results you are after.
Best regards,
Trowa
Aug 17, 2014 at 12:11 AM
Thank you for your patience. I took the file home on the weekend and finally got it work. Thank you very much.
Regards
Capricorn56