F5F Stay Refreshed Software General Software Issue encountered with DATE formula in Google Sheets for conditional formatting.

Issue encountered with DATE formula in Google Sheets for conditional formatting.

Issue encountered with DATE formula in Google Sheets for conditional formatting.

D
DodoBirdy
Member
Find
50
12-01-2023, 08:52 AM
#1
I hope this is a good place to post this, I have tried posting in a couple of other places but so far have not received a reply and I am on a time limit here.
All I'm trying to do, I have a workbook that has two date columns... lets say B2 and K2. All I'm trying to do is setup a conditional formatting rule that will change K2 to RED if the date in that cell is 45 days past the date in B2. (And do the same for the rest of the rows below... so for B12 and K12, same thing... if K12 is 45 days past B12 I just want K12 to be highlighted in RED.
I can use the default TODAY, YESTERDAY, TOMORROW presets and get it to work like I want, but when I select EXACT DATE I cannot figure out the formula to input to make it do the right calculations. Could anyone please help? I think it sounds like it should be fairly simple, but I have been at this for hours today with no luck. (well, with all bad luck anyway).
Thanks!
Reply
Reply
D
DodoBirdy
12-01-2023, 08:52 AM #1

I hope this is a good place to post this, I have tried posting in a couple of other places but so far have not received a reply and I am on a time limit here.
All I'm trying to do, I have a workbook that has two date columns... lets say B2 and K2. All I'm trying to do is setup a conditional formatting rule that will change K2 to RED if the date in that cell is 45 days past the date in B2. (And do the same for the rest of the rows below... so for B12 and K12, same thing... if K12 is 45 days past B12 I just want K12 to be highlighted in RED.
I can use the default TODAY, YESTERDAY, TOMORROW presets and get it to work like I want, but when I select EXACT DATE I cannot figure out the formula to input to make it do the right calculations. Could anyone please help? I think it sounds like it should be fairly simple, but I have been at this for hours today with no luck. (well, with all bad luck anyway).
Thanks!

Reply
Reply
M
MilkyMike
Member
Find
65
12-01-2023, 09:51 AM
#2
Update your post to reflect your cell formulas and progress so far. What methods have you already attempted?
It makes sense to consider date functions when dealing with dates, along with logical operations.
View the task as a sequence of actions.
Step 1: Configure your IF THEN ELSE condition based on the 45-day rule. The ELSE part might not be necessary.
Step 2: Next, implement another IF THEN ELSE to adjust the cell color or font color to red when the condition is met. This could be achieved with nested IF statements, but splitting the requirement into simpler steps will simplify the logic.
Tip (in plain language): If K2 minus B2 exceeds 45, update the corresponding cell to red.
After setting up the process for one row, simply copy the formulas down to the next rows.
Reply
Reply
M
MilkyMike
12-01-2023, 09:51 AM #2

Update your post to reflect your cell formulas and progress so far. What methods have you already attempted?
It makes sense to consider date functions when dealing with dates, along with logical operations.
View the task as a sequence of actions.
Step 1: Configure your IF THEN ELSE condition based on the 45-day rule. The ELSE part might not be necessary.
Step 2: Next, implement another IF THEN ELSE to adjust the cell color or font color to red when the condition is met. This could be achieved with nested IF statements, but splitting the requirement into simpler steps will simplify the logic.
Tip (in plain language): If K2 minus B2 exceeds 45, update the corresponding cell to red.
After setting up the process for one row, simply copy the formulas down to the next rows.

Reply
Reply