Count number of days in date range that fall in current week

Hi everyone,

I have a number of project plans in Smartsheet and am trying to get a summary of how many task hours each person has assigned for the current week.

The plans currently capture task Start Date, End Date, and number of hours required to complete the task.

As some tasks will take multiple weeks to complete, I need a way to calculate how any days within a date range fall in the current week.

(i.e. Start Date = 08/16 & End Date = 08/27; Total workdays = 10; Total workdays in current week = 5)

I cannot seem to figure out a way to do this. Any help would be greatly appreciated!

Find more posts tagged with

Formulas

Reports

functionality

Project Management

Accepted answers

MCorbin

I created helper columns to determine 1) if the date range includes the current week and 2) to set the start and finish dates if the date range isn't entirely within the current week. Then I used those helper columns to calculate the # of days in the current week:

Formulas:

Start Date or Sunday: =IF(OR([Start Date]@row > TODAY(7 - WEEKDAY(TODAY())), [End Date]@row < TODAY(1 - WEEKDAY(TODAY()))), "", IF([Start Date]@row <= TODAY(1 - WEEKDAY(TODAY())), TODAY(1 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Saturday: =IF(ISDATE([Start Date or Sunday]@row), IF(AND([End Date]@row >= TODAY(1 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(7 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(7 - WEEKDAY(TODAY()))))

Formula for # of Days this week: =IF(ISDATE([Start Date or Sunday]@row), NETDAYS([Start Date or Sunday]@row, [End Date or Saturday]@row), 0)

If you only wanted Monday - Friday, you'd modify the first 2 formulas like this:

Start Date or Monday: =IF(OR([Start Date]@row > TODAY(6 - WEEKDAY(TODAY())), [End Date]@row < TODAY(2 - WEEKDAY(TODAY()))), "", IF([Start Date]@row <= TODAY(2 - WEEKDAY(TODAY())), TODAY(2 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Friday: =IF(ISDATE([Start Date or Sunday]@row), IF(AND([End Date]@row >= TODAY(2 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(6 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(6 - WEEKDAY(TODAY()))))

All comments

MCorbin

Formulas:

Formula for # of Days this week: =IF(ISDATE([Start Date or Sunday]@row), NETDAYS([Start Date or Sunday]@row, [End Date or Saturday]@row), 0)

If you only wanted Monday - Friday, you'd modify the first 2 formulas like this:

Eric James

I put this into my project plan and it looks to have worked perfectly! Thanks so much @MCorbin !

rread

This was great! Such a help, thank you. I made two adjustments from your samples.

Start Date or Monday:

=IF(OR([Start Date]@row > TODAY(6 - WEEKDAY(TODAY())), ([End Date]@row < TODAY(2 - WEEKDAY(TODAY())))), "", IF([Start Date]@row <= TODAY(2 - WEEKDAY(TODAY())), TODAY(2 - WEEKDAY(TODAY())), [Start Date]@row))

End Date or Friday

=IF(ISDATE([Start Date or Monday]@row), IF(AND([End Date]@row >= TODAY(2 - WEEKDAY(TODAY())), [End Date]@row <= TODAY(6 - WEEKDAY(TODAY()))), [End Date]@row, TODAY(6 - WEEKDAY(TODAY()))))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.