If today is between a start and end date


Forgive me if this has been covered. Everything I have found has been close but not what I am trying to do.

I have a sheet with visit start and end dates to populate a calendar.

I want to write a formula on another sheet that adds up the total from the revenue column for every job that falls on today using the scheduled start and end dates.

I would then use this cell to populate information on my dashboard.

Could someone point me to the right article or explain the formula for me to do this?

See image of sheet below:

Best Answer


  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/24/23 Answer ✓

    @Chood Try this:

    =SUMIFS({Revenue}, {Scheduled Start Date}, <TODAY(), {Scheduled End Date}, >TODAY())

  • Chood
    Chood ✭✭

    I got invalid value.

    Here is what I entered, and I made the new references to the other sheet:

    =INDEX(COLLECT({Jobs Revenue}, {Jobs Start Date}, <TODAY(), {Jobs End Date}, TODAY()), 1)

    However, I do have blank fields in this column. Could it be getting hung up on the blanks?

  • Chood
    Chood ✭✭

    I got it to work with the following..Also, I just referenced the same column "End Date" and everything worked fine. I figure the end date was plenty good.

    =SUMIFS({Jobs Revenue}, {Jobs End Date}, @cell >= DATE(2023, 5, 7), {Jobs End Date}, @cell <= DATE(2023, 5, 13))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!