topic Re: Formula for Calculating Remaining # of Work Weeks in Month, Quarter, Year in App Development

Formula for Calculating Remaining # of Work Weeks in Month, Quarter, Year

joshdellapietro — Fri, 15 Jan 2021 17:09:12 GMT

Hi,

I am trying to measure how much sales we need to average a week for the rest of the month, quarter, and year to meet our budget. For example, if we need $400m to meet our budget for January and there is only 2 work weeks left of January, I want the formula to equate to $200m.

With that being said, I need a formula to tell me how many work weeks remain in the month, quarter, or year. Even if the work week is not a full week and just a Monday, I want it to still count as 1.

Re: Formula for Calculating Remaining # of Work Weeks in Month, Quarter, Year

GaryGiles — Fri, 15 Jan 2021 22:34:17 GMT

For weeks remaining in the month, use:

=Week(monthend(today()),0)-week(today(),0)

for quarter: =Week(Quarterend(today()),0)-week(today(),0)

for year: =Week(Yearend(today()),0)-week(today(),0)

The 0 in the 2nd parameter of the Week function means the week starts on Monday. I think that is what you need, but you might have to adjust.

Re: Formula for Calculating Remaining # of Work Weeks in Month, Quarter, Year

joshdellapietro — Fri, 22 Jan 2021 17:25:57 GMT

This should work, my only concern may be is if the new month starts with a Saturday (ex. May 2021). I wouldn't want that Saturday to be considered a week.

Re: Formula for Calculating Remaining # of Work Weeks in Month, Quarter, Year

joshdellapietro — Fri, 22 Jan 2021 18:28:21 GMT

It is also not counting weeks if they start on any day other than Monday

Re: Formula for Calculating Remaining # of Work Weeks in Month, Quarter, Year

GaryGiles — Fri, 22 Jan 2021 18:51:07 GMT

@joshdellapietro

Okay, this should make the necessary adjustment for Saturday and Sunday and the partial week at the end.

=Week(MonthEnd(Today()),0)-Week(Date(Today()+Match(WeekDay(Today()),'Sun','Sat')))+1

You'll want to change the QuarterEnd and YearEnd