List of date formula I have used repeatedly

L_123L_123 ✭✭✭✭✭
edited 07/07/21 in Best Practice
05/20/20 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

Comments

  • @[email protected]

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

  • Sarah DonnellySarah Donnelly ✭✭✭✭✭

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

  • L_123L_123 ✭✭✭✭✭
    edited 05/21/20

    next month returned as integer (option 2)

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


  • L_123L_123 ✭✭✭✭✭

    quarter of date in column "Date" current row returned as Q120 for quarter 1 year 2020.

    ="Q" + INT((MONTH([email protected]) + 2) / 3) + RIGHT([email protected], 2)

  • L_123L_123 ✭✭✭✭✭

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

    =int([email protected] - [email protected])

  • L_123L_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_123L_123 ✭✭✭✭✭
    edited 06/03/20

    Outputs the current week as sunday - saturday

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

  • L_123L_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) - [email protected] < 0.864, "Red", "Green")

  • L_123L_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_123L_123 ✭✭✭✭✭
    edited 11/12/20

    Returns the hours out of a created or modified column as integer

    =VALUE(SUBSTITUTE(MID([email protected], FIND(":", [email protected]) - 2, 2), " ", ""

    Returns hours out of created or modified column in military time as integer

    =VALUE(SUBSTITUTE(MID([email protected], FIND(":", [email protected]) - 2, 2), " ", "")) + IF(RIGHT([email protected], 2) = "PM", 12, 0

  • L_123L_123 ✭✭✭✭✭

    Returns the week number by month

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

  • L_123L_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 GuzmanSandra Guzman ✭✭✭✭✭

    Hello @[email protected]!


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

    thinkspi.com

  • L_123L_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([email protected]), MONTH([email protected]), 18 + IF(WEEKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1) - 1) = 1, 2, 9) - WEEKDAY(DATE(YEAR([email protected]), MONTH([email protected]), 1) - 1

Sign In or Register to comment.