Working Days across a Date range

Del Horne

Hello friends!

I'm hoping to have a column which automatically populates the number of working days in a given period from a [Start Date] and [Number of Months] columns.

e.g.

Start Date = 3rd July 2023

Number of Months = 2

Result = 45 days

• 63 calendar days – 18 days skipped: 9 Saturdays, 9 Sundays

Would anyone have a solution to this already? I'd be forever grateful!! 🙌

Thank you!

Del

Paul Newcome

You'll need something like this:

=NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row) + ROUNDDOWN((MONTH([Start Date]@row) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Start Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH([Start Date]@row) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH([Start Date]@row)), 1, 0), IF(MOD(MONTH([Start Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH([Start Date]@row) - ABS([Number Of Months]@row) < 1, YEAR([Start Date]@row) - 1, YEAR([Start Date]@row)), IF(MONTH([Start Date]@row) - ABS([Number Of Months]@row) < 1, MONTH([Start Date]@row) + (12 - ABS([Number Of Months]@row)), MONTH([Start Date]@row) - ABS([Number Of Months]@row)), DAY([Start Date]@row))))

Del Horne

Wow! Let me try and digest this!! I'll let you know 👌

Paul Newcome

Basically... I already had an EDATE formula put together that allows you to put a variable (but whole) number of months in one column and get the date coming off of the Start Date.

Using that to output the "end date" for the NETWORKDAYS function should give you what you need.