How to get previous week number of the month and quarter number from a specific date that WILL NOT return a 0

JHud2022

New Member
Joined
Dec 16, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello!

I am trying to get the previous week number of a month and the previous quarter number based on a specific date, without returning a 0 when the date is at the beginning of the month.

The week number of the month formula that I am using and works perfectly is,
Excel Formula:
=WEEKNUM(B3)-WEEKNUM(DATE(YEAR(B3),MONTH(B3),1))+1
, where B3 is the cell that contains the specific date. The altered formula to find the previous week number of the month is,
Excel Formula:
=WEEKNUM(B3)-WEEKNUM(DATE(YEAR(B3),MONTH(B3),1))
The altered formula works when the week number is anything other than 1, as this returns a 0. I am trying to figure out how to get it to return the previous week number, which in this case would be the previous week from the last month. So, if the date is March 1, then the formula for the previous week should be the week number of February 28. I have tried to change the ",1))" to other numbers as well to see if I was overthinking it, and unfortunately I didn't come up with the solution that I am looking for.

The same thing is happening when I try to find the previous quarter. The formula that works to find the current quarter is
Excel Formula:
=ROUNDUP(MONTH(B3)/3,0)
but when I try to get the previous quarter when the current quarter is 1, I get a 0 when I use
Excel Formula:
=ROUNDUP(MONTH(B3)/3,0)-1

I feel like I am overthinking this and the solution is very simple.

Any ideas on this?

Thank you so much for any help possible, I really appreciate it!

Thank you!

Jacob
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,223,961
Messages
6,175,651
Members
452,664
Latest member
alpserbetli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top