Friday to Friday pull

Dustin Whitehead
Dustin Whitehead ✭✭✭✭

Hello SmartSheet World! 😊

I am in need of yet more assistance ....

I would like to build a formula that would pull some data from Friday to Friday opposed to just using a 7 day week pull.

Here is a sample of my data

This is the formula I am currently using but I have to keep changing it every day so that it correctly pulls data from a data column from Friday to Friday

Any help would be greatly appreciated, if you need more data, just let me know:

=COUNTIFS({CPM On-Call Form Range 1}, [Primary Column]@row, {CPM On-Call Form Range 3}, >TODAY(-7))

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer βœ“

    @Dustin Whitehead

    Whoops. You're correct, I messed up my signs, my bad.

    =COUNTIFS({CPM On-Call Form Range 1}, [Primary Column]@row, {CPM On-Call Form Range 3}, and(@cell <= (today() - weekday(today())-1),@cell >= (today() - weekday(today()) - 8

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    =TODAY() - WEEKDAY(TODAY()) - 1

    will get you last friday

    =TODAY() - WEEKDAY(TODAY()) - 8

    will get you the friday before last

    Combine to get

    =COUNTIFS({CPM On-Call Form Range 1}, [Primary Column]@row, {CPM On-Call Form Range 3}, and(@cell >= (today() - weekday(today())-1),@cell <= (today() - weekday(today()) - 8


    (*Untested*)

  • Dustin Whitehead
    Dustin Whitehead ✭✭✭✭

    @L@123, this was definitely a great start however, it seems that formula will only pull data that is on the Friday, I need it to pull all data between the two Fridays, for example I would need all data from 7/16 through 7/23 and now that it is a new week, next week will show from 7/23 to 7/30.

    When I entered your formula into the sheet, it took something that had a count down to a 0 instead of the correct function. However, now I have a formula that can be manipulated better to account for what I need, thank you πŸ™‚

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer βœ“

    @Dustin Whitehead

    Whoops. You're correct, I messed up my signs, my bad.

    =COUNTIFS({CPM On-Call Form Range 1}, [Primary Column]@row, {CPM On-Call Form Range 3}, and(@cell <= (today() - weekday(today())-1),@cell >= (today() - weekday(today()) - 8

  • Dustin Whitehead
    Dustin Whitehead ✭✭✭✭

    @L@123 THANK YOU VERY MUCH!!! That definitely did it, I really appreciate it!!πŸ˜€

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!