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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!