Counting dates before the latest date in a range

[STEVENS3010]

Hi all,

I have a spreadsheet where one of the columns contains a list of dates and I'm looking for a formula to count any dates more than one day prior.

For example... I have =today in cell a1 and it displays 28/03/2021 (today's date). In Column B I have a list of dates (example below) and I need a formula to count any dates more than one day prior i.e. anything before 27/03/2021. Is this possible?

Example List:
28/03/2021
01/03/2021
25/02/2021
16/02/2021

 

[Toadstool]

Hi Stevens3010,

This should work:

=COUNTIF(B1:$B$9999,"<"&TODAY()-1)

 

[STEVENS3010]

Hi Stevens3010,

This should work:

=COUNTIF(B1:$B$9999,"<"&TODAY()-1)

This is great, thank you. Do you know if it is possible to amend the forumla for working days? For example, I have a list of dates, but I wish to ignore anything from today and the previous working day, and then count everything else. Is this possible?

 

[Fluff]

How about

=COUNTIFS(B:B,"<"&WORKDAY(TODAY(),-1))

 

[STEVENS3010]

How about

=COUNTIFS(B:B,"<"&WORKDAY(TODAY(),-1))

Brilliant! Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.