# List of date formula I have used repeatedly

Options
✭✭✭✭✭✭
edited 07/07/21

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

```
Tags:
«1

• Employee
Options

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 05/21/20
Options

next month returned as integer (option 2)

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

=int(modified@row - created@row)

• ✭✭✭✭✭✭
edited 05/28/20
Options

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

• ✭✭✭✭✭✭
edited 06/03/20
Options

Outputs the current week as sunday - saturday

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

• ✭✭✭✭✭✭
edited 06/05/20
Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
edited 11/12/20
Options

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

• ✭✭✭✭✭✭
Options

Returns the week number by month

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 08/26/21
Options

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