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,
, where B3 is the cell that contains the specific date. The altered formula to find the previous week number of the month is,
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
but when I try to get the previous quarter when the current quarter is 1, I get a 0 when I use
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
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
Excel Formula:
=WEEKNUM(B3)-WEEKNUM(DATE(YEAR(B3),MONTH(B3),1))
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)
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: