Solved Increment date by weeks accounting for 5 week months

Discussion in 'Business Applications' started by dpic44, Mar 28, 2019.

Not open for further replies.

Joined:
Mar 8, 2019
Messages:
4
I'm creating a list of dates by week, the first week of the year will be input manually and the rest of the year will automatically populate. Since there are 4 months that have 5 weeks I need to show 5 rows per month.

I can get it to leave the 5th week blank on the first 4 week month, but I can't get the first week of the following month to populate correctly if it follows a 5 week month. I only want it to leave the 5th week blank if it's a 4 week month.

=IFS(A4="",A3+7,AND(A4<>"",MONTH(A4+7)=MONTH(A4)),A4+7,MONTH(A4+7)<>MONTH(A4),"")

Week
01/07/2019
01/14/2019
01/21/2019
01/28/2019
blank
02/04/2019
02/11/2019
02/18/2019
02/25/2019
blank
03/04/2019
03/11/2019
03/18/2019
03/25/2019
blank
04/01/2019
04/08/2019
04/15/2019
04/22/2019
04/29/2019

2. etafModerator

Joined:
Oct 2, 2003
Messages:
65,211
First Name:
Wayne
in A2 put the date 7/1/19
then you can use in A3
=IF(A2="",A1+7,IF(MONTH(A2)=MONTH(A2+7),A2+7,""))
and copy down
NOTE I'm in UK, so used UK based dates DD/MM/YY
but should work OK in US dste format

Attached Files:

• Dates with gap etaf.xlsx
File size:
10.6 KB
Views:
1

Joined:
Mar 8, 2019
Messages:
4
Hi Wayne! Thanks for the quick reply. I did it this way first, where it breaks down is after a 5 week month. I don't want a space after a 5 week month, only a 4 week month (5 row block for each month). I'm working with a forecast and each month has 5 rows. This spreadsheet will be used for each year so the 5 week months will change and we only want to change the first date and have everything populate down from there. I should have been more explicit in my post.

Here's the result from the formula above ... there is a blank after July but I don't want one there. I'm really puzzled
06/04/2018
06/11/2018
06/18/2018
06/25/2018
Blank okay here
07/02/2018
07/09/2018
07/16/2018
07/23/2018
07/30/2018
Don't want blank here
08/06/2018
08/13/2018
08/20/2018
08/27/2018
Blank okay here
09/03/2018
09/10/2018
09/17/2018
09/24/2018
Blank okay here
10/01/2018
10/08/2018
10/15/2018
10/22/2018
10/29/2018
Don't want blank here
11/05/2018
11/12/2018
11/19/2018
11/26/2018
Blank okay here
12/03/2018
12/10/2018
12/17/2018
12/24/2018
12/31/2018
Don't want blank here
01/07/2019
01/14/2019
01/21/2019
01/28/2019
Blank okay here
02/04/2019
02/11/2019
02/18/2019
02/25/2019
Blank okay here
03/04/2019
03/11/2019
03/18/2019
03/25/2019
Blank okay here
04/01/2019
04/08/2019
04/15/2019
04/22/2019
04/29/2019

43591 This is the last row, not sure why I got this.

Last edited: Mar 28, 2019

Joined:
Mar 8, 2019
Messages:
4
Maybe we need to check for the number of weeks in a month first, or use COUNT ...

I created named ranges for the 5 rows in each month, Jun_Dates, Jul_Dates, etc. Is there a way to incorporate that into the formula?

Last edited: Mar 28, 2019
5. etafModerator

Joined:
Oct 2, 2003
Messages:
65,211
First Name:
Wayne
IN A3 put
=IF(A2="",A1+7,IF(OR(SUM(INT((WEEKDAY(A2-DAY(A2)+1-2)+(DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-1))/7))=5,MONTH(A2)=MONTH(A2+7)),A2+7,""))
and copy down
should work as required

Attached Files:

• Dates with gap etaf (1).xlsx
File size:
11.6 KB
Views:
0
dpic44 likes this.

Joined:
Mar 8, 2019
Messages:
4
@etaf, I cannot thank you enough for helping me with this. It works like a charm. I tried it for fiscal year 6/2018-5/2019 and also for fiscal year 6/2019-5/2020. It is perfect.

I have them putting the beginning of the fiscal year in A2. A4 is the first week of production so A4 = A2. Then I added your formula to A5 and copied it down. I'm locking all cells except A2 so it can't be changed. Thanks again!

7. etafModerator

Joined:
Oct 2, 2003
Messages:
65,211
First Name:
Wayne
you are very welcome

8. suckhoe24gio

Joined:
Mar 31, 2019
Messages:
2
I should have been more explicit in my post.

As Seen On