if function with dates

Maz Uddin

Hello fellow people

@Paul Newcome @Genevieve P. @'in you both as you always been helpful to me

i have a list of dates in a date column as per screenshot below

I need to know out of which of those are within the next 12 months

i would really appreciate your input please

Paul Newcome

If you just want the count, you don't need the helper column. It would look something like this:

=COUNTIFS([Date Column]:[Date Column], AND(@cell>= TODAY(), @cell<= DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY()))))

Paul Newcome

@Maz Uddin In that case you would replace the [Column Name]:[Column Name] range with a {Cross Sheet Reference}.

Paul Newcome

@Maz Uddin Are you trying to flag each row individually or count how many are there?

Either way, the date portion of whatever formula is used would be

DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY()))

Nick Korna

Hi @Maz Uddin,

You've a couple of options here:

If you want exactly 12 months (to the day):

=IF(AND(Date@row - TODAY() <= 366, TODAY() - Date@row < 0), "Yes", "No")

In this formula the 366 is because we're in a leap year - you can amend to 365 otherwise.

If you're not picky about the days and just want to see it in terms of months (so get a positive result back for any date where the month is 12 months from today's month):

=IF(OR(AND(MONTH(TODAY()) - MONTH(Date@row) >= 0, YEAR(TODAY()) - YEAR(Date@row) <= 1), (AND(MONTH(TODAY()) - MONTH(Date@row) < 0, YEAR(TODAY()) - YEAR(Date@row) = 0))), "Yes", "No")

Sample output:

Hope this helps, but if you've any problems/questions then just post them! 🙂

Maz Uddin

@Paul Newcome i dont mind creating a helper column and then run a countif(s) fuction on that column to pick up dates within next 12 months

Paul Newcome

If you just want the count, you don't need the helper column. It would look something like this:

=COUNTIFS([Date Column]:[Date Column], AND(@cell>= TODAY(), @cell<= DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY()))))

Maz Uddin

@Paul Newcome i forgot to tell you, i have a source sheet with a date column, so I want to do this function in another sheet.

I think the formula will need a tweak right?

Maz Uddin

@Nick Korna Thanks for the formula, it works well!

Paul Newcome

@Maz Uddin In that case you would replace the [Column Name]:[Column Name] range with a {Cross Sheet Reference}.