Best Of
TIP - Find the date of the first Sunday of the month
This little formula is useful for a range of solutions, like working out if a date is in daylight savings time or not. The examples below reference a date field named [Date].
The first part of the formula finds the date of the seventh day of the month. This is done so that the number of days from Sunday can be subtracted:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 )
The second part of the formula finds the number of days to subtract from that date:
- ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )
This part of the formula gets the date of the seventh day of the month again, and works out what day of the week it is. We subtract 1 from the result because the WEEKDAY() function returns values starting at Sunday = 1. So if the seventh day of the week just happens to be Sunday we don't want to subtract 1, we need to subtract 0 instead.
So the entire formula to find the date of the first Sunday of a month is:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )
This can be tweaked a little for applications like checking if a date is/was in daylight savings time. This example expects daylight savings time to start on the first Sunday in October and end on the first Sunday in April of the next year:
=IF( AND( [Date]@row >= DATE( YEAR( [Date]@row ), 10, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), 10, 7 ) ) - 1 ), [Date]@row < DATE( YEAR( [Date]@row ) + 1, 4, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row) + 1, 4, 7) ) - 1) ), 1, 0)
It checks to see if [Date] is greater than or equal to the first Sunday in October (month 10) in the same year as [Date] AND is less than the fist Sunday of April (month 4) in the following year. If that is true then it returns 1 (daylight savings), otherwise 0 (not daylight savings).
To find the date of the first day of the month for some other weekday a slight tweak is required on the second part of the formula:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - MOD( ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 ) + [Weekday], 7)
where [Weekday] contains:
- 0 for Sunday
- 1 for Monday
- 2 for Tuesday
- 3 for Wednesday
- 4 for Thursday
- 5 for Friday
- 6 for Saturday
John C Murray
Re: Share Data Shuttle Workflows
I think both the ability to track and know who created a Data Shuttle workflow, and transfer them to another user, are mission critical issues that Smartsheet needs to address, no matter how many votes this gets in this Community. If Data Shuttle workflows break--or otherwise need to be changed/updated--and you cannot find them or access them because a user has left your company or been deleted, it could bring an entire organization to a halt. This is a major vulnerability that must be addressed.
Tyson N
Re: ENGAGE 23 Logbook - Share & connect!
Best experience of Engage 23?
I made it official! Iβm a full-on Smartsheet addict.
Darla Brown
Re: ENGAGE 23 Logbook - Share & connect!
I learned that the Zamboni is electric! And I'm excited to learn more about Dynamic View, Dashboards, and all the cool new features that are coming soon!
Carrie Barnett
Re: ENGAGE 23 Logbook - Share & connect!
Looking forward to #GettingENGAGED with other super users!
smartshaay
Re: Smartsheet Travel Diaries Bonus Giveaway - Win a Travel Kit!
More swag!! I'm 100% here for all the merch I can get. π
Darla Brown
Re: Our first ENGAGE challenge is here - Smartsheet Travel Diaries πΊοΈ + Bonus Giveaway
Made it to the office for a change..... I seem to have gone a little overboard at Engage last year with the stickers π Let's see what I can add this year....
#HowToIrritateYourITManager
MCorbin
Create an alert when there is no activity in the sheet
If there is no activity in the sheet in the past "X" days, we should be able to create an automation to alert someone. It is really important for the sheets that are updating automatically so if the automation is broken, the user can be notified,
Shikha
Search functionality / widget in Dashboards
The search functionality within widgets on Smartsheet dashboards does not exist, and we have not seen that CTRL+F is reliable enough. In order to increase user experience with dashboards, we'd like there to be a widget search option that will search for text within any widgets on the dashboard.
mstoa
Checklist Report
Hello,
In my smartsheet, I have something of a checklist with all checklist items on a new row. Each item is accompanied by about 5+ columns for "Checklist Item" "Complete?" "Due Date" "Close Date" "Notes" and so on. The checklist is to be duplicated for each new program we launch and contains 124 checklist items.
Since we can manage over 50 launch programs, it would be convenient to have a feature to combine all of my program checklists into one master sheet, with the same columns repeating for each program. This would help us avoid opening 50+ different sheets every day.
My idea is that you could simply condense each program into a single column when you're not viewing it, then expand the program in the same fashion.
This seems to be a common issue with others who manage tollgates and checklists in smartsheets and use the control center or have multiple duplicate checklists for multiple programs.
Alex Carter