I'm trying to create a formula for the below information:

find if in a range of start dates and end dates of a specific job, one or more of these dates correspond to the current week date

if that's happen the guy is not available otherwise is available for this week



  • Mike TV
    Mike TV ✭✭✭✭✭✭


    First we have to define the date range for this week. I've done that by first figuring out which day of the week it is.

    Day of Week formula:


    Start of Week formula:

    =TODAY(-[Day of Week]@row) + 1

    End of Week formula:

    =TODAY(-[Day of Week]@row) + 7

    Finally, we can write a formula to checkmark a box if the Start Date or End Date falls within the Start of Week or End of Week days:

    =IF(OR(AND([Start Date]@row >= [Start of Week]@row, [Start Date]@row <= [End of Week]@row), AND([End Date]@row >= [Start of Week]@row, [End Date]@row <= [End of Week]@row)), 1, IF(AND([Start Date]@row < [Start of Week]@row, [End Date]@row > [End of Week]@row), 1))

  • thanks for helping @Mike TV

    unfortunately i can not have a column representing the current week, indeed i was asking for a formula which contain the current week, like for example weeknumber(TODAY())

    is possible to use this formula you just sent me but using weeknumber(TODAY()) instead pf the current week's column??

  • Mike TV
    Mike TV ✭✭✭✭✭✭


    Unfortunately, I do not know of a way to do this with zero helper columns. Maybe someone else can guide you down that direction. I'm not sure that's possible. You know you can hide your helper columns so they're not displayed, right?

  • @Mike TV

    no actually i did not know that, but is the formula going to update every week by itself in this way?

    because i need a formula able to update the weekly availability of the guys

  • Mike TV
    Mike TV ✭✭✭✭✭✭


    Yes it will update automatically because it is using the TODAY() function a lot.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!