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

[joshdellapietro]

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.

[GaryGiles]

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.

[joshdellapietro]

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. 

[joshdellapietro]

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

[GaryGiles]

@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