Formula to mark the checkbox for the newest date in a row

leahsmartsheet

I have two columns in a form: Current Status and Date of Completion. The Date of Completion column should be updated on a weekly basis.

I want the Current Status to check off based on the latest Date of Completion. Is there a formula to do this?

Protonsponge

Hello @leahsmartsheet,

Thank you for the clarification 😀 If i understand correctly, you would like the check box to tick if the [Date of Completion] is within the current week - e.g. the form was populated this week? If that is what you are looking for, I have put some possible options below. I hope one of those will be what you are looking for but please let me know if you are looking for something different.

Date of Completion is within the last 7 days:-

=IF(TODAY() - [Date of Completion]@row <= 7, 1, 0)

Date of Completion is within the last 5 working days:-

=IF(NETWORKDAYS([Date of Completion]@row, TODAY()) <= 5, 1, 0)

Date of Completion is within the current week:-

=IF(AND(WEEKNUMBER([Date of Completion]@row) = WEEKNUMBER(TODAY()), YEAR([Date of Completion]@row) = YEAR(TODAY())), 1, 0)

I hope that is helpful to you in someway.

Protonsponge

Protonsponge

Hello @leahsmartsheet - Can I clarify on what criteria you would like the current status to check? If the date is within a certain range, or simply if there is a date entered in the form for date of completion?

Protonsponge

leahsmartsheet

https://community.smartsheet.com/discussion/comment/451017#Comment_451017

Hello! A new date is entered every week into this column. I want a formula to check off the latest date. So I don't believe that would fall into a specific range. Thank you!

Protonsponge

Hello @leahsmartsheet,

Thank you for the clarification 😀 If i understand correctly, you would like the check box to tick if the [Date of Completion] is within the current week - e.g. the form was populated this week? If that is what you are looking for, I have put some possible options below. I hope one of those will be what you are looking for but please let me know if you are looking for something different.

Date of Completion is within the last 7 days:-

=IF(TODAY() - [Date of Completion]@row <= 7, 1, 0)

Date of Completion is within the last 5 working days:-

=IF(NETWORKDAYS([Date of Completion]@row, TODAY()) <= 5, 1, 0)

Date of Completion is within the current week:-

=IF(AND(WEEKNUMBER([Date of Completion]@row) = WEEKNUMBER(TODAY()), YEAR([Date of Completion]@row) = YEAR(TODAY())), 1, 0)

I hope that is helpful to you in someway.

Protonsponge

leahsmartsheet

Thank you @Protonsponge!! This answers my questions.