Date range formula

Options
edited 12/09/19

I am trying to write a date range formula with an IF AND statement (I think).

I need to check a box if [Previous date] is in a specific date range.

• ✭✭
Options

It should look something like this:

=IF(AND([Previous date]@row <= DATE(2019, 1, 31), [Previous date]@row >= DATE(2019, 1, 1)), 1, 0)

This one will flag all dates in January 2019. You'll need to adjust the dates manually. The 1 and 0 at the end of the formula turn the checkbox on and off.

• ✭✭✭✭✭✭
Options

When you only have two results (checked or unchecked), you really only need to specify what changes the default of unchecked to checked. You could leave the 0 off of the end of the formula and it will still work the same way. (Just FYI).

.

A few additional things to keep in mind that may or may not make things a little easier for you...

.

To help with having to manually adjust dates within a formula (if you're anything like me you stand a good chance of a typo or fat fingering something haha), you could reference 3 (or 6) different cells depending on the type of breakdown you are looking for. Something along the lines of (column headers are bold)...

.

Helper          Start          End

Year

Month

Day

.

You could then use (assuming the Year row is row 1)

=DATE(Start\$1, Start\$2, Start\$3)

.

This gives you the ability to edit your dates without having to edit your formula. If you are looking for weekly, monthly or yearly counts you could use

=IF(WEEKNUMBER([Previous Date]@row) = *********, 1)

In the section of ********* you have a few options. You could enter a specific number, WEEKNUMBER(a cell reference that would contain a number), or WEEKNUMBER(a date using the DATE function which could also be manual entry or cell references).

.

=IF(MONTH([Previous Date]@row) = ************, 1)

.

=IF(YEAR([Previous Date]@row) = ***********, 1)

.

=IF(AND(YEAR([Previous Date]@row) = **********, MONTH([Previous Date]@row) = **********), 1)