CODEWITHSUNDEEP
excelexcel-formula
Deke
Deke

Reputation: 495

Excel Formula to highlight range based on date range

I'm trying to figure out how to create a formula to call out an entire range where there is a date less or equal to today's date.

In this example, I have a number 600259 (Column A) where there are dates that are less than today's date. I would like my formula to mark ALL as true instead of just the rows where the dates are less than.

enter image description here

My current formula is:

=COUNTIFS(A:A,A60,I:I,I60) 

=COUNTIFS(A:A,A60,I:I,"<=12/7/2023")

I know I'm close but I think I need a second set of eyes to help

Upvotes: 1

Views: 116

Answers (1)

Grimidk
Grimidk

Reputation: 44

I may be misunderstanding your question but if you want to mark all dates in the past (less than today) try doing this instead

=IF(A1 >= TODAY(),TRUE)

where A1 is the date you want to check

you can then count the ones that are true with:

=COUNTIF(A:A,TRUE)

A being your date column

if you want to you can combine them both into:

=COUNTIF(A:A,">="&TODAY())

Hope this helps

EDIT: after you explanation I believe this is what you're looking for:

enter image description here

Using the formulas from above you'll need to add a few new columns either in this sheet or an auxiliary one.

An ID column, a COUNT column and a IF column but you can merge this last pair

For the count column you'll need:

=COUNTIFS(I:I,">="&TODAY(),H:H,K3)

where I is the date column and H is the ID column

for the if you'll just need:

=IF(L3>0,TRUE)

where L3 is the count right beside it

you can merge this two into:

=IF(COUNTIFS(I:I,">="&TODAY(),H:H,K3)>0,TRUE)

Upvotes: 0

Related Questions