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
-
@Chood Try this:
=SUMIFS({Revenue}, {Scheduled Start Date}, <TODAY(), {Scheduled End Date}, >TODAY())
Answers
-
@Chood Try this:
=SUMIFS({Revenue}, {Scheduled Start Date}, <TODAY(), {Scheduled End Date}, >TODAY())
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!