Tasks extending from one calendar year into the next

When trying to determine the number of workdays in a month, the following equation stops working when you cross from December of one year into January of the next. Any ideas as to what I'm missing/doing wrong? Thanks in advance!

The equation below is for January, I have equations for all the other months of the year but with the appropriate start and end numbers for the individual months (ie, April would have 4,1 and 4,30 in the equation)

=IF(AND($[Start Date]@row <= DATE(YEAR($[Start Date]@row ), 1, 31), $[End Date]@row >= DATE(YEAR($[Start Date]@row ), 1, 1)), NETWORKDAYS(MAX($[Start Date]@row , DATE(YEAR($[Start Date]@row ), 1, 1)), MIN($[End Date]@row , DATE(YEAR($[Start Date]@row ), 1, 31))) * 8, 0)

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @MartinTB — you're on the right track!

The issue you're seeing (especially when tasks span from December into January) is a common one. Your current formula anchors the January date range using the YEAR([Start Date]), which breaks when the task starts in December of the previous year. That’s why tasks like 2024/12/20 – 2025/01/10 don’t return January workdays — even though they clearly span into that month.

This is a practical need. We often want to know how many hours or days are spent in each month for a task that spans across multiple months — whether for reporting, billing, or resource planning.

To handle this, I created a reusable structure that works for any month and year, using a [Month #] column and a Year# sheet summary field. It dynamically calculates the number of working days (and optionally working hours) that fall within the target month, even if the task starts before and ends after that month.

Key point: You don't need to write formulas like DATE(YEAR(...), 1, 31) manually for each month — the logic adapts automatically.

Here’s a more flexible solution I’ve built and tested:

https://app.smartsheet.com/b/publish?EQBCT=0181c9d2cc5046dbad98841a3ac57dad

It calculates how many working days (and optionally hours) fall within a given month, based on a [Month #] column (e.g., 1 for January) and a Year# sheet summary field (e.g., 2025). This method checks for actual date overlap with the month range — so it still counts workdays even when neither the start nor end date falls inside the month, as long as the task spans it.

Column Setup:

[Month #]: Target month number (1–12)
Year#: Sheet summary field with the year (e.g., 2025)
[Start Date] and [End Date]: Task dates
[End of Month]: Helper to find last day of the month
[Days]: Workdays in the month
[Hours] (optional): Days × 8

[End of Month]:

=IF([Month #]@row  < 12, DATE(Year#, [Month #]@row  + 1, 1) - 1, DATE(Year# + 1, 1, 31))

[Days]:

=IF(

  AND(ISDATE([Start Date]@row), ISDATE([End Date]@row)),

  IF(

    AND(

      [End Date]@row >= DATE(Year#, [Month #]@row, 1),

      [Start Date]@row <= [End of Month]@row

    ),

    NETWORKDAY(

      MAX([Start Date]@row, DATE(Year#, [Month #]@row, 1)),

      MIN([End Date]@row, [End of Month]@row)

    ),

    ""

  ),

  ""

)

[Hours] (optional):

=IF(ISNUMBER([Days]@row ), [Days]@row  * 8)

No-helper version (optional):

=IF(

  AND(ISDATE([Start Date]@row), ISDATE([End Date]@row)),

  IF(

    AND(

      [End Date]@row >= DATE(Year#, [Month #]@row, 1),

      [Start Date]@row <= IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1)

    ),

    NETWORKDAY(

      MAX([Start Date]@row, DATE(Year#, [Month #]@row, 1)),

      MIN([End Date]@row, IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1))

    ),

    ""

  ),

  ""

)

This approach ensures:

No hardcoding of month start/end dates
Correct results even when a task crosses years
Accurate working day and hour breakdowns per month

Tip: For [Start Date] and [End Date], you may want to use values from the parent row — for example, putting the project's overall start and end dates in the parent and letting each child row calculate days and hours per month.
Alternatively, you can reference dates from a separate sheet, bringing the values into the parent row or sheet summary fields to centralize the logic.

MartinTB

Thanks for response, I'll give it a shot and let you know how it goes!

MartinTB

@jmyzk_cloudsmart_jp, any chance you can help me with your solution? Not sure I understand it. For instance, the equation you wrote to calculate the workdays in a month, see row 2 in your solution above, the task spans from late December to mid-February, where does it show the workdays for December (2 workdays) and January (22 workdays)? Feb should be 10 workdays.

jmyzk_cloudsmart_jp

Hi @MartinTB

Sorry for the confusion.

In the former example, I used the NETWORKDAY.

In the function, "Returns the number of working days between two dates. Adds 1 day to the result if the start date is a non-working day."

https://help.smartsheet.com/function/networkday

The updated formula;

[Days]=IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row )), IF(AND([End Date]@row >= DATE(Year#, [Month #]@row , 1), [Start Date]@row <= IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1)), NETWORKDAYS(MAX([Start Date]@row , DATE(Year#, [Month #]@row , 1)), MIN([End Date]@row , [End of Month]@row )), ""), "")
[Days without helper columns] =IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row )), IF(AND([End Date]@row >= DATE(Year#, [Month #]@row , 1), [Start Date]@row <= IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1)), NETWORKDAYS(MAX([Start Date]@row , DATE(Year#, [Month #]@row , 1)), MIN([End Date]@row , IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1))), ""), "")

[Days without helper columns: with US Holidays] =IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row )), IF(AND([End Date]@row >= DATE(Year#, [Month #]@row , 1), [Start Date]@row <= IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1)), NETWORKDAYS(MAX([Start Date]@row , DATE(Year#, [Month #]@row , 1)), MIN([End Date]@row , IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1)), {Federal_Holidays_2025 : Date}), ""), "")

Also, I added [Month # Start] and [Month # End] helper columns, which correspond to the MAX and MIN parts of the above formulas;

[Month # Start] =MAX([Start Date]@row , DATE(Year#, [Month #]@row , 1))
[Month # End] =MIN([End Date]@row , IF([Month #]@row < 12, DATE(Year#, [Month #]@row + 1, 1) - 1, DATE(Year# + 1, 1, 1) - 1))

https://app.smartsheet.com/b/publish?EQBCT=5501722be17b4c92bc7671943f437cc9

The [Days without helper columns: with US Holidays] formula is the one specifically excluding US federal holidays, referencing this sheet.

MartinTB

Thanks jmyzk_cloudsmart_jp

I think I should have started with showing you how my sheet is set up and THEN asking the question about how to write a formula that calculates the days/hours in each month when a task extends over multiple months AND also extends from one calendar year to the next. Please see attached snippet of my sheet. I'm unsure how to get what you've done above to work in my sheet. Is it possible to schedule an appointment with you to talk through it? Martin

tasks extending months.png

jmyzk_cloudsmart_jp

@MartinTB

I updated the solution using your format.

It uses the same method for getting the last day of a given month,

 DATE(Year#, n + 1, 1) - 1)

I also fixed the error in my original formula for tasks that start before the start of a month and end after the end of the month.

=IF(
  AND([Start Date]@row  < DATE(Year#, 1, 1), [End Date]@row  > DATE(Year#, 1 + 1, 1) - 1), 
    NETWORKDAYS(DATE(Year#, 1, 1), DATE(Year#, 1 + 1, 1) - 1, {Federal_Holidays_2025 : Date}),

https://app.smartsheet.com/b/publish?EQBCT=355056aa833e42068350e6314749b169

I forgot to add the *hours/day or *8, for example, at the end of each formula. Please add the appropriate hours/day for your organization.

[Jan Hours] =IF(AND([Start Date]@row < DATE(Year#, 1, 1), [End Date]@row > DATE(Year#, 1 + 1, 1) - 1), NETWORKDAYS(DATE(Year#, 1, 1), DATE(Year#, 1 + 1, 1) - 1, {Federal_Holidays_2025 : Date}), IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row )), IF(AND([End Date]@row >= DATE(Year#, 1, 1), [Start Date]@row < DATE(Year#, 1 + 1, 1) - 1), NETWORKDAYS(MAX([Start Date]@row , DATE(Year#, 1, 1)), MIN([End Date]@row , DATE(Year#, 1 + 1, 1) - 1), {Federal_Holidays_2025 : Date}), 0), 0))
[Feb Hours] =IF(AND([Start Date]@row < DATE(Year#, 2, 1), [End Date]@row > DATE(Year#, 3, 1) - 1), NETWORKDAYS(DATE(Year#, 2, 1), DATE(Year#, 3, 1) - 1, {Federal_Holidays_2025 : Date}), IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row )), IF(AND([End Date]@row >= DATE(Year#, 2, 1), [Start Date]@row < DATE(Year#, 3, 1) - 1), NETWORKDAYS(MAX([Start Date]@row , DATE(Year#, 2, 1)), MIN([End Date]@row , DATE(Year#, 3, 1) - 1), {Federal_Holidays_2025 : Date}), 0), 0))
[March Hours] =IF(AND([Start Date]@row < DATE(Year#, 3, 1), [End Date]@row > DATE(Year#, 4, 1) - 1), NETWORKDAYS(DATE(Year#, 3, 1), DATE(Year#, 4, 1) - 1, {Federal_Holidays_2025 : Date}), IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row )), IF(AND([End Date]@row >= DATE(Year#, 3, 1), [Start Date]@row < DATE(Year#, 4, 1) - 1), NETWORKDAYS(MAX([Start Date]@row , DATE(Year#, 3, 1)), MIN([End Date]@row , DATE(Year#, 4, 1) - 1), {Federal_Holidays_2025 : Date}), 0), 0))
[ .....]

[Dec Hours] =IF(AND([Start Date]@row < DATE(Year#, 12, 1), [End Date]@row > DATE(Year# + 1, 1, 1) - 1), NETWORKDAYS(DATE(Year#, 12, 1), DATE(Year# + 1, 1, 1) - 1, {Federal_Holidays_2025 : Date}), IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row )), IF(AND([End Date]@row >= DATE(Year#, 12, 1), [Start Date]@row < DATE(Year# + 1, 1, 1) - 1), NETWORKDAYS(MAX([Start Date]@row , DATE(Year#, 12, 1)), MIN([End Date]@row , DATE(Year# + 1, 1, 1) - 1), {Federal_Holidays_2025 : Date}), 0), 0))

P/S

I am away from the office until August 12th.

If you still need video chat, propose reasonable times after that. My time zone is Tokyo, JST, GMT +9.

MartinTB

Thank you for this jmyzk_cloudsmart_jp

Will the above equations work when tasks extend from one year to the next? ie, task starts in November of 2025 and goes to March 2026.

jmyzk_cloudsmart_jp

Just change Year# to Year# +1.

https://app.smartsheet.com/b/publish?EQBCT=355056aa833e42068350e6314749b169&_gl=117cws3e_gcl_auOTk0MTUyMjA0LjE3NTIwMjY3NTI._gaOTgxMjE2MjAyLjE3MTI4ODIxMjE._ga_ZYH7XNXMZK*czE3NTQ5NTM2OTgkbzEyMzAkZzEkdDE3NTQ5NTgxNDUkajI3JGwwJGgw

=IF(AND([Start Date]@row < DATE(Year# + 1, 3, 1), [End Date]@row > DATE(Year# + 1, 4, 1) - 1), NETWORKDAYS(DATE(Year# + 1, 3, 1), DATE(Year# + 1, 4, 1) - 1, {Federal_Holidays_2025 : Date}), IF(AND(ISDATE([Start Date]@row), ISDATE([End Date]@row)), IF(AND([End Date]@row >= DATE(Year# + 1, 3, 1), [Start Date]@row < DATE(Year# + 1, 4, 1) - 1), NETWORKDAYS(MAX([Start Date]@row, DATE(Year# + 1, 3, 1)), MIN([End Date]@row, DATE(Year# + 1, 4, 1) - 1), {Federal_Holidays_2025 : Date}), 0), 0))

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.