conditional formating using cells containing dates

C

cebubum

I want to have missed due dates automatically highlighted using conditional
fromating. Example:
A1 is due date, dd/mm/yy
A2 is completion date, dd/mm/yy
If A2 does not contain a value on the A1 date, I want the A1 date to turn a
color.
When the A2 date is entered, and the value is later than the A1 date, I want
both A! and A2 to be a specific color.

Any help would be appreciated.
 
B

Biff

Hi!

Select cells A1 and A2.
Format>Conditional Formatting
Formula is: =AND(A$1=TODAY(),A$2="")
Select your desired style for the first condition.
Click the ADD button.
Condition 2
Formula is: =A$2>A$1
Select your desired stye for condition 2.
OK out

Biff
 
D

David

Biff wrote
Select cells A1 and A2.
Format>Conditional Formatting
Formula is: =AND(A$1=TODAY(),A$2="")
Select your desired style for the first condition.
Click the ADD button.
Condition 2
Formula is: =A$2>A$1
Select your desired stye for condition 2.
OK out
Click to expand...

Hmm. Maybe you can help me
In my sheet, A5:A55 contains names
C4:G4 contains current weekdays, formatted "ddd"
C5:G55 contains minutes of attendance
How can I highlight the names in A of persons who attended today?

I can do it for one day by selecting A5:A55 and applying:
=AND(C$4=TEXT(TODAY(),"ddd"),C5>0) (works only for Mon)
I want it to work for whatever the current day is: Tue would be D5, Wed
would be E5, Thu would be F5, Fri would be G5
 
B

Biff

Hi!

That was a tough one!

In C4 enter this formula and format as DDD:

=TODAY()-WEEKDAY(TODAY(),3)

In D4 enter =C4+1 and copy across to G4.

Select the names range, A5:A55
Format>Conditional Formatting
Formula is:

=INDEX(C$4:G$4,MATCH(1,(WEEKDAY(C$4:G$4,2)=WEEKDAY(TODAY
(),2))*(C5:G5>0),0))>0

Select your desired format style(s).
OK out

Biff
 
D

David

Biff wrote
That was a tough one!

In C4 enter this formula and format as DDD:

=TODAY()-WEEKDAY(TODAY(),3)

In D4 enter =C4+1 and copy across to G4.

Select the names range, A5:A55
Format>Conditional Formatting
Formula is:

=INDEX(C$4:G$4,MATCH(1,(WEEKDAY(C$4:G$4,2)=WEEKDAY(TODAY
(),2))*(C5:G5>0),0))>0

Select your desired format style(s).
OK out
Click to expand...

Wow!! Works great!! Had no clue it would be that complex. Thanks.

I did have to adjust my CF for C5:S55 to handle real dates so the minutes
value would be highlighted as well. That was the easy part.

One minor question remains to make me extremely happy. Can the 'DDD' values
be made all upper case? I tried adding =UPPER in front of the formula in
C4, but that just switched the result to a serial date.
 
B

Biff

Hi!

I don't think that's possible. The DDD or ddd format is
just that, a format. The true underlying value is the
numeric serial date.

But then on the other hand, someone a lot smarter than me
may be able to come up with a custom format that will do
that.

I guess you could use an all uppercase text string for the
weekdays but then the cf formula would *REALLY* be complex.

Biff
 
D

David

Biff wrote
I guess you could use an all uppercase text string for the
weekdays but then the cf formula would *REALLY* be complex.
Click to expand...

Agreed. My mind has been spinning while experimenting with that very thing.
But then I've always been one to want have my cake and eat it, too <g>.

Again, thanks for all your effort on my behalf.
 
D

David

Biff wrote
Well, if it's not too late....
Click to expand...

Patience and tenacity are traits I hold dear.
Conditional Formatting
Formula is:

=INDEX(C$4:G$4,MATCH(1,(C$4:G$4=TEXT(WEEKDAY(TODAY
(),1),"DDD"))*(C5:G5>0),0))=TEXT(WEEKDAY(TODAY(),1),"DDD")
Click to expand...

BINGO!!!! Kudos to you for sticking with me on this quest.
Can I offer you some cake? <G>
 
D

David

BTW, I was able to reduce it slightly:

=INDEX(C$4:G$4,MATCH(1,(C$4:G$4=TEXT(TODAY(),"DDD"))*(C5:G5>0),0))=TEXT
(TODAY(),"DDD")
 
D

David

Biff wrote
Seems to me that this should not require such a "complex"
formula
Click to expand...

Should have thought of this in the first place:

=SUMPRODUCT((C$4:G$4=TEXT(TODAY(),"DDD"))*(C5:G5>0))
 
C

ChrisLouie

This worked PERFECTLY for me. Thank you so much. I have one extra question...
Is it possible to make this work so that the condition chosen includes the
day of AND the day after.....
For example:
A1 = Due Date
A2 = Complettion Date (this date could be equal to or greater then the due
date)
Can the formula include this so that it doesn't have to be either/or?
 
Top