Hi All,

A client wants to be able to see how many calls were logged in thier current working week . However the working week begins on a Friday 00:00am and ends Thursday 11:59pm.

I'm after a formula to count all entries with the word "Aberdeen" in the Column called "Club" in the mentioned working week . This counter would then be reset when the new working week begins

I have the Count formula : =COUNTIFS(Club:Club, CONTAINS("Aberdeen", @cell))

I just need to include the Working Week range to the formaula .



  • Eric_
    Eric_ ✭✭✭
    edited 02/15/24

    You need to modify the WEEKNUMBER() value based on the WEEKDAY() value.

    At the time of writing this email, we are Thu 15-Feb, real week 7. What will tomorrow (Friday) be in your mind, week 7 or week 8?

    If tomorrow is week 7:

    In Smartsheet, the week starts on Monday (Day 2), at least for me in Europe. For you it starts on Friday (Day 6). So in your logic, Day 6, 7 and 1 are "real week" and the next Day 2-5 are "real week"-1

    New week number =IF(OR(WEEKDAY(Date@row) > 5, WEEKDAY(Date@row) = 1), WEEKNUMBER(Date@row), WEEKNUMBER(Date@row) - 1)

    If tomorrow, we are already Week 8, then

    New week number =IF(OR(WEEKDAY(Date@row) > 5, WEEKDAY(Date@row) = 1), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))

    You can add this value to a new "Week" column and then count how many occurences of the call's week matches the same week number for the given Club.

    Something like:

    =COUNTIFS(Club:Club, Club@row, Week:Week, Week@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!