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:

Find more posts tagged with

Accepted answers

ShelbyWarren

@Chood Try this:

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

All comments

ShelbyWarren

@Chood Try this:

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

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

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))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.