# if function with dates

Options
✭✭✭✭✭

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

Tags:

• ✭✭✭✭✭✭
Options

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()))))

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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()))

• ✭✭✭✭✭✭
Options

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! 🙂

• ✭✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

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()))))

• ✭✭✭✭✭
Options

@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?

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭