List of date formula I have used repeatedly
Doing this so I have an ongoing list of functioning optimized formulas , keeping it in the community in case it helps someone else. Please feel free to comment with your own, but I'm keeping the most basic of the formulas out of this. Below are just some of the ones that I thought of now, i'll comment more as I use them.
CURRENT PERIOD
this sunday returned as date
=today()-weekday(today())+1
this quarter returned as integer 1-4 standard year
=INT((MONTH(TODAY()) + 2) / 3
NEXT PERIOD
next quarter returned as integer 1-4 standard year (also current quarter if year starts in october)
=IF(INT((MONTH(TODAY()) + 2) / 3) = 4, 1, INT((MONTH(TODAY()) + 2) / 3)+1
next month returned as integer
=if(month(today())=12,1,month(today(
LAST PERIOD
last month returned as integer
=month(date(year(today()),month(today()),1)-1
last quarter standard year returned as integer 1-4 (also current year if year starts in july)
=IF(INT((MONTH(TODAY()) + 2) / 3) = 1, 4, INT((MONTH(TODAY()) + 2) / 3)-1
Comments
-
This is wonderful, thank you for sharing! Looking forward to seeing your future formulas/comments as well.
-
Thank you for sharing! Newer user (1year) and these types of formulas have presented a challenge for me.
-
next month returned as integer (option 2)
=month(date(year(today()),month(today()),28)+4
-
quarter of date in column "Date" current row returned as Q120 for quarter 1 year 2020.
="Q" + INT((MONTH(Date@row) + 2) / 3) + RIGHT(Date@row, 2)
-
Returns the number of hours between the system columns created and modified as integer.
=int(modified@row - created@row)
-
Dealing with created and/or modified columns
returns difference between 2 cells in format D: H: M: S:
="D:" + INT(Created2 - Created1) + " H:" + INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36) + " M:" + INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) + " S:" + INT(60 * (((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) - INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)))
Return each individually:
Days:
INT(Created2 - Created1)
Hours
INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)
Minutes
INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)
Seconds
INT(60 * (((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) - INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)))
-
Outputs the current week as sunday - saturday
=TODAY() - WEEKDAY(TODAY()) + 1 + "-" + (TODAY() - WEEKDAY(TODAY()) +7
-
Returns Boolean RG if the most recent modification to the sheet was within 24 hours of the created date on the same row (To the Second)
=IF(MAX(Modified:Modified) - Created@row < 0.864, "Red", "Green")
-
Returns first day of month 2 months prior to current
=DATE(IF(MONTH(TODAY()) <= 2, YEAR(TODAY()) - 1, YEAR(TODAY())), MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 32), 1)
-
Returns the hours out of a created or modified column as integer
=VALUE(SUBSTITUTE(MID(Created@row, FIND(":", Created@row) - 2, 2), " ", ""
Returns hours out of created or modified column in military time as integer
=VALUE(SUBSTITUTE(MID(Created@row, FIND(":", Created@row) - 2, 2), " ", "")) + IF(RIGHT(Created@row, 2) = "PM", 12, 0
-
Returns the week number by month
=ROUNDUP((WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + DAY(TODAY())) / 7)
-
Returns the date that a specific day of the current week falls on. *you can change today() in the formula to a reference to a date
Sun
=today()-weekday(today())+1
Mon
=today()-weekday(today())+2
Tue
=today()-weekday(today())+3
Wed
=today()-weekday(today())+4
Thu
=today()-weekday(today())+5
Fri
=today()-weekday(today())+6
Sat
=today()-weekday(today())+7
-
Hello @L@123!
Thank you so much for sharing these formulas proactively with us! I have created an internal document that I have started tracking my own formulas once I started to build out reports.
I think it would be great if we can have a place where we could add formulas in addition to the ones that we already have access to (courtesy of Smartsheet). Think I will have to submit an enhancement request for this.
All the best,
Sandra
-
@Sandra Guzman This is definitely a great thread! I also have a thread here in the community that is geared towards time based solutions. If you have a collection of formulas/solutions that are based on a common theme (such as these date formulas), feel free to start one of your own threads if you are willing to share.
-
Returns the 3rd friday of the month specified by the date in column A. *can be used to return x weekday of any month by changing the 18
=DATE(YEAR(A@row), MONTH(A@row), 18 + IF(WEEKDAY(DATE(YEAR(A@row), MONTH(A@row), 1) - 1) = 1, 2, 9) - WEEKDAY(DATE(YEAR(A@row), MONTH(A@row), 1) - 1
