List of date formula I have used repeatedly

L_123
L_123 ✭✭✭✭✭✭
edited 07/07/21 in Best Practice

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

«1

Comments

  • Genevieve P.
    Genevieve P. Employee Admin

    @L@123

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

  • Sarah Donnelly
    Sarah Donnelly ✭✭✭✭✭

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

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 05/21/20

    next month returned as integer (option 2)

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


  • L_123
    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
    L_123 ✭✭✭✭✭✭

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

    =int(modified@row - created@row)

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 05/28/20

    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
    L_123 ✭✭✭✭✭✭
    edited 06/03/20

    Outputs the current week as sunday - saturday

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

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 06/05/20

    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
    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
    L_123 ✭✭✭✭✭✭
    edited 11/12/20

    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
    L_123 ✭✭✭✭✭✭

    Returns the week number by month

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

  • L_123
    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
    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
    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
    L_123 ✭✭✭✭✭✭
    edited 08/26/21

    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