Fill 7 rows and increment and reset based on date

kyddrivers

New Member
Joined
Mar 22, 2013
Messages
42
I am in need of a formula to return a week of month value. Starting with the 15th of the month as 1, and repeat for 7 days or rows and then increment until we get to the 15th of the next month and restart back at 1.

I have INT((ROW()-ROW($F$1))/7)+1) to get the 7 rows and increment by 1, but I am failing to get the reset when I hit the 15th. If I add =IF(DAY(C34)=15, 1, to beginning it returns a 1 on the 15 and goes back to incrementing.

example: Formula in column E =IF(DAY(C28)=15, 1, INT((ROW()-ROW($E$28))/7)+1)

Col C​
Col E
15-Jan-22​
1
16-Jan-22​
1
17-Jan-22​
1
18-Jan-22​
1
19-Jan-22​
1
20-Jan-22​
1
21-Jan-22​
1
22-Jan-22​
2
23-Jan-22​
2


Here is what happens when I get to Feb 15th:
11-Feb-22​
4
12-Feb-22​
5
13-Feb-22​
5
14-Feb-22​
5
15-Feb-22​
1
16-Feb-22​
5
17-Feb-22​
5

How do I reset back to 1 starting on the 15th of the month?

Thanks in advance!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Book1
AB
1513-Jan-20215
1614-Jan-20215
1715-Jan-20211
1816-Jan-20211
1917-Jan-20211
2018-Jan-20211
2119-Jan-20211
2220-Jan-20211
2321-Jan-20211
2422-Jan-20212
2523-Jan-20212
2624-Jan-20212
2725-Jan-20212
2826-Jan-20212
2927-Jan-20212
3028-Jan-20212
3129-Jan-20213
3230-Jan-20213
3331-Jan-20213
3401-Feb-20213
3502-Feb-20213
3603-Feb-20213
3704-Feb-20213
3805-Feb-20214
3906-Feb-20214
4007-Feb-20214
4108-Feb-20214
4209-Feb-20214
4310-Feb-20214
4411-Feb-20214
4512-Feb-20215
4613-Feb-20215
4714-Feb-20215
4815-Feb-20211
4916-Feb-20211
5017-Feb-20211
5118-Feb-20211
5219-Feb-20211
5320-Feb-20211
5421-Feb-20211
5522-Feb-20212
5623-Feb-20212
5724-Feb-20212
5825-Feb-20212
5926-Feb-20212
6027-Feb-20212
6128-Feb-20212
6201-Mar-20213
6302-Mar-20213
6403-Mar-20213
6504-Mar-20213
6605-Mar-20213
6706-Mar-20213
Sheet1
Cell Formulas
RangeFormula
B15:B67B15=IF(DAY(A15)>=15,CEILING(A15-DATE(YEAR(A15),MONTH(A15),14),7)/7,CEILING(A15-DATE(YEAR(A15),MONTH(A15)-1,14),7)/7)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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
Top