Automatic Checkbox Based on 3 Sets of Dates

plaistedcj

I'm trying to get a checkbox to check automatically if the day in progress is within a given month, but has not been completed in the same month and has not been placed on hold after the day in the month that it was placed in progress. Any help would be grateful!!

If the project was dated as "In Progress" on 6/15/2022 (helper date column already created for date in progress)

Completed date was not in June or before June (helper date column already created for date completed)

On Hold date is not equal to 6/15/2022 or between 6/15/2022 and 6/30/2022 (helper date column already created for date on hold)

Mike TV

@plaistedcj

Please provide the name of the columns in question. Too often people cannot get formulas to work if they can't just copy-paste them into their sheet from the forums.

plaistedcj

I created 2 checkbox columns for each month labeled In Progress and On Hold (i.e. Jan'22 In Progress, Jan'22 On Hold). I then have 3 date columns, one for when the project was put into the In Progress, On Hold and my date completed (using the automation to record a date).

Date Completed Date In Progress Date On Hold Jan'22 In progress (checkbox) Jan'22 On Hold (checkbox)

Paul Newcome

Are you able to provide some screenshots?

plaistedcj

plaistedcj

I'm trying to get the "On Hold/Declined In September" checkbox to check if the date is in the same month as In Progress, but the day is between 9/26 and 9/30.

Paul Newcome

So to do this specifically for September 2022 you would use a formula such as this:

=IF([Date of Status Change (On Hold/Declined)]@row <> "", IF(AND([Date of Status Change (On Hold/Declined)]@row>= [Date of Status Change (In Progress)]@row, [Date of Status Change (On Hold/Declined)]@row<= DATE(2022, 09, 30)), 1))

plaistedcj

That works great for the On Hold/Declined! How would I write the first IF statement for the In Progress checkboxes?

Paul Newcome

You would use something like this...

=IF([Date of Status Change (In Progress)]@row <> "", IF(AND(MONTH([Date of Status Change (In Progress)]@row) = 9, YEAR([Date of Status Change (In Progress)]@row) = 2022), 1)