List of date formula I have used repeatedly

L_123

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

Genevieve P.

@L@123

This is wonderful, thank you for sharing! Looking forward to seeing your future formulas/comments as well.

Sarah Donnelly

Thank you for sharing! Newer user (1year) and these types of formulas have presented a challenge for me.

L_123

next month returned as integer (option 2)

=month(date(year(today()),month(today()),28)+4

L_123

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)

L_123

Returns the number of hours between the system columns created and modified as integer.

=int(modified@row - created@row)

L_123

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)))

L_123

Outputs the current week as sunday - saturday

=TODAY() - WEEKDAY(TODAY()) + 1 + "-" + (TODAY() - WEEKDAY(TODAY()) +7

L_123

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")

L_123

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)

L_123

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

L_123

Returns the week number by month

=ROUNDUP((WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + DAY(TODAY())) / 7)

L_123

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

Sandra Guzman

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

Paul Newcome

@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.

L_123

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