Conditional Format IF Today is Monday AND Cell Value is within 3 days

[kaeroku]

Hello,

I am trying to write a formula for conditional formatting against a column of date values in a spreadsheet for tracking overdue and new items on a spreadsheet. I need a formula that will detect when it is Monday, and conditionally format everything that has been added new to the spreadsheet since Friday by highlighting it yellow.

I already have a formula which does this during other weekdays... (range is $F:$F, format conditions are highlight cell: yellow)

=TODAY()-F1<=1

And I was hoping to apply the same logic to capture Mondays as well. This is what I've tried:

=AND((WEEKDAY(NOW)=2),(TODAY()-F1<=3))

or

=(TODAY()-F1<=3)&(WEEKDAY(NOW)=2)

or

=(TODAY()-F1<=3)&(WEEKDAY(F1)=1,6,7)


None of these appear to have any effect whatsoever. It seems like this should be rather simple, and maybe I'm overthinking it, but I've been messing with this for days and haven't come up with anything. Help please?

Thanks a lot!

[NBVC]

Try:

=AND(WEEKDAY(TODAY())=2,TODAY()-F1<=3)

[benishiryo]

hi kaeroku,

not sure if i understand your question right. so only Mondays of today's week & future weeks will get highlighted? i did up a sample for your reference. i made Today's date in Column G a fixed date just for the eg. u can use Column H's formula if it's right.

[kaeroku]

Try:

=AND(WEEKDAY(TODAY())=2,TODAY()-F1<=3)

Thank you for your reply.

Like the others I've tried, this does not return an error, but has no effect.

For testing purposes, I'm replacing the 2 from "(TODAY())=" with a 5 (Thursday), and referencing three cells with dates 4/20/2012, 4/23/2012, 4/26/2012, because it's very easy to adapt that for Monday usage.

hi kaeroku,

not sure if i understand your question right. so only Mondays of today's week & future weeks will get highlighted? i did up a sample for your reference. i made Today's date in Column G a fixed date just for the eg. u can use Column H's formula if it's right.

Basically, whenever it is Monday, I want to highlight all cells with date values since Saturday morning.

I already have: whenever it is not Monday, highlight all cells with date values within 24 hours.

The purpose is to capture all new items which come in from the prior workday, and on Mondays, capture Friday of the previous workweek in order to fulfill this requirement.

[NBVC]

So I put those 3 dates in D1 to F1 and applied conditional formula:

=AND(WEEKDAY(TODAY())=2,TODAY()-$F1<=3)

note the $ anchoring the F column value so that each cell is compared to the F column.

The 3 cells were then highlighted.

[kaeroku]

So I put those 3 dates in D1 to F1 and applied conditional formula:

=AND(WEEKDAY(TODAY())=2,TODAY()-$F1<=3)

note the $ anchoring the F column value so that each cell is compared to the F column.

The 3 cells were then highlighted.

I copied your formula over into my test document (and replaced 2 with 5). Firstly, only two of the cells should be highlighted, as 4/20/2012 is more than 3 days before today. When I apply the formula, only one cell is affected - the 4/26/2012 cell - which is also incorrect.

Seems we're making progress though, getting *any* highlighting at all is better than I had.

Something which I'm not sure is right: "anchoring the F column value so that each cell is compared to the F column."
--I want to compare to Today's date, not the date shown. In my spreadsheet, the only date values are dates of reports of defective items, all in the F column. The formula should be checking those dates against today's date to apply formatting based on "TODAY()-$F1<=3"

My test document is attached, I'm hoping that helps explain the desired effect more clearly.

[kaeroku]

I made a mistake. With weekday=5, it works. I thought I had changed 2 to 5.

Thank you. As others here have said, you are amazing. How do I promote you?