How do you change the month to reflect a specific pay date range?

[shinexcel]

I am looking to set the months up within the period of my pay days, so to properly reflect my spending within that period.
I get paid on 26th of each a month, so I would like to set it so that each month starts from 26th.

e.g.

July 2022. - Pay Period: 26.07.22 - 25.08.22

Aug 2022 - Pay Period: 26.08.22 - 25.09.22

Sep 2022 - Pay Period: 26.09.22 - 25.10.22

so on and so forth...

However, in the months which the 26th falls on a weekend, the pay date changes to the nearest preceding week-day.
So following from the example above the next pay period would be:

Oct 2022 - Pay Period: 26.10.22 - 24.11.22. not... 26.10.22 - 25.11.22
...as Nov 26th 2022 falls on a weekend, so therefore pay date is the nearest preceding week-day, which is 25.11.22.

So the Pay period after that would be .... Nov 2022 - Pay Period: 25.11.22 - 25.12.22


Hope this make sense. If not please ask for clarification.


Question:
How can I set this up in excel?

I ideally want to have an input table where when I type in September - the month covers a range of my specific pay periods...

....

..


I am relatively new to excel so my know-how is very limited. All solutions welcome.


Thanks in advance

 

[SanjayGMusafir]

Check this -

Note:

• I have used formulae every where to keep dates dynamic
• Rather than using "Jan" if you will use "M 01" as month, the reference can be used to identify the time frame.
• Whole concept is to give you some idea, rest you have explore as per your needs
• Still if you need any further help, feel free to ask.

All Records.xlsb
	A	B	C
1	Start	End	Month
2	1/26/22	2/26/22	M 01
3	2/26/22	3/26/22	M 02
4	3/26/22	4/26/22	M 03
5	4/26/22	5/26/22	M 04
6	5/26/22	6/26/22	M 05
7	6/26/22	7/26/22	M 06
8	7/26/22	8/26/22	M 07
9	8/26/22	9/26/22	M 08
10	9/26/22	10/26/22	M 09
11	10/26/22	11/26/22	M 10
12	11/26/22	12/26/22	M 11
13	12/26/22	1/26/23	M 12
Sheet2

Cell Formulas
Range	Formula
A2	A2	=DATE(YEAR(TODAY()),1,26)
B2:B13	B2	=EDATE(A2,1)
C2:C13	C2	="M "&TEXT(MONTH(A2),"00")
A3	A3	=DATE(YEAR(TODAY()),2,26)
A4	A4	=DATE(YEAR(TODAY()),3,26)
A5	A5	=DATE(YEAR(TODAY()),4,26)
A6	A6	=DATE(YEAR(TODAY()),5,26)
A7	A7	=DATE(YEAR(TODAY()),6,26)
A8	A8	=DATE(YEAR(TODAY()),7,26)
A9	A9	=DATE(YEAR(TODAY()),8,26)
A10	A10	=DATE(YEAR(TODAY()),9,26)
A11	A11	=DATE(YEAR(TODAY()),10,26)
A12	A12	=DATE(YEAR(TODAY()),11,26)
A13	A13	=DATE(YEAR(TODAY()),12,26)

 

[shinexcel]

Check this -

Note:

• I have used formulae every where to keep dates dynamic
• Rather than using "Jan" if you will use "M 01" as month, the reference can be used to identify the time frame.
• Whole concept is to give you some idea, rest you have explore as per your needs
• Still if you need any further help, feel free to ask.

All Records.xlsb
	A	B	C
1	Start	End	Month
2	1/26/22	2/26/22	M 01
3	2/26/22	3/26/22	M 02
4	3/26/22	4/26/22	M 03
5	4/26/22	5/26/22	M 04
6	5/26/22	6/26/22	M 05
7	6/26/22	7/26/22	M 06
8	7/26/22	8/26/22	M 07
9	8/26/22	9/26/22	M 08
10	9/26/22	10/26/22	M 09
11	10/26/22	11/26/22	M 10
12	11/26/22	12/26/22	M 11
13	12/26/22	1/26/23	M 12
Sheet2

Cell Formulas
Range	Formula
A2	A2	=DATE(YEAR(TODAY()),1,26)
B2:B13	B2	=EDATE(A2,1)
C2:C13	C2	="M "&TEXT(MONTH(A2),"00")
A3	A3	=DATE(YEAR(TODAY()),2,26)
A4	A4	=DATE(YEAR(TODAY()),3,26)
A5	A5	=DATE(YEAR(TODAY()),4,26)
A6	A6	=DATE(YEAR(TODAY()),5,26)
A7	A7	=DATE(YEAR(TODAY()),6,26)
A8	A8	=DATE(YEAR(TODAY()),7,26)
A9	A9	=DATE(YEAR(TODAY()),8,26)
A10	A10	=DATE(YEAR(TODAY()),9,26)
A11	A11	=DATE(YEAR(TODAY()),10,26)
A12	A12	=DATE(YEAR(TODAY()),11,26)
A13	A13	=DATE(YEAR(TODAY()),12,26)

Thanks for taking time out to put this together . I will try this and let you know.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to change month format to a specific date range?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[shinexcel]

How to change month format to a specific date range?

If any one could help on this the would be great...follow link to ExcelQuestion.xlsx