Formula to mark the checkbox for the newest date in a row
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?
Best Answer
-
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
Answers
-
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
-
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!
-
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
-
Thank you @Protonsponge!! This answers my questions.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
