Need help with some date formulas

[Vkackley3300]

I have a worksheet where the user inputs a start date in cell B2 and then numbers of months in cells C5-C9 to create a calendar schedule as noted below. I have written formulas in cells A5 thru B9 to calculate start and end dates based on the user input.

I want to take this data and create a monthly calendar of sorts as I have noted in cells C13 thru C60. I have inputted the start date in cell C13. Beginning in cell C14, I would like a formula that will input the first day of the following month unless the date changes mid-month in cells B5-C9 in which case it will catch that and enter the day following the date change. I have manually changed the dates in cells C26, C39 and C52 to demonstrate what I am trying to do. From this, I can then calculate the number of days in each period as I have done in column D.

HDR Financial Template 4.29.2020.xlsm
	A	B	C	D
1
2	Start date:	2/10/2020
3
4	Start	End	# Months
5	2/10/2020	4/9/2020	2
6	4/10/2020	2/24/2021	10.5
7	2/25/2021	2/24/2022	12
8	2/25/2022	2/24/2023	12
9	2/25/2023	10/9/2023	7.5
10
11
12			Period Start	Days in Period
13			2/10/2020	20
14			3/1/2020	31
15			4/1/2020	9
16			4/10/2020	21
17			5/1/2020	31
18			6/1/2020	30
19			7/1/2020	31
20			8/1/2020	31
21			9/1/2020	30
22			10/1/2020	31
23			11/1/2020	30
24			12/1/2020	31
25			1/1/2021	55
26			2/25/2021	4
27			3/1/2021	31
28			4/1/2021	30
29			5/1/2021	31
30			6/1/2021	30
31			7/1/2021	31
32			8/1/2021	31
33			9/1/2021	30
34			10/1/2021	31
35			11/1/2021	30
36			12/1/2021	31
37			1/1/2022	31
38			2/1/2022	24
39			2/25/2022	4
40			3/1/2022	31
41			4/1/2022	30
42			5/1/2022	31
43			6/1/2022	30
44			7/1/2022	31
45			8/1/2022	31
46			9/1/2022	30
47			10/1/2022	31
48			11/1/2022	30
49			12/1/2022	31
50			1/1/2023	31
51			2/1/2023	24
52			2/25/2023	4
53			3/1/2023	31
54			4/1/2023	30
55			5/1/2023	31
56			6/1/2023	30
57			7/1/2023	31
58			8/1/2023	31
59			9/1/2023	30
60			10/1/2023	8
61
Sheet3 (2)

Cell Formulas
Range	Formula
A5	A5	=B2
B5:B9	B5	=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9	A6	=B5+1
C13	C13	=A5
C53:C60,C40:C51,C27:C38,C17:C25,C14:C15	C14	=DATE(YEAR(C13),MONTH(C13)+1,1)
D13:D60	D13	=IF(ISBLANK(C14),B$9-C13,C14-C13)

 

[Snakehips]

@Vkackley3300 I have spent far to much of my lockdown hours trying to solve this than is sensible. Time that I could well have spent watching repeats on TV ! ?

Always close but, no cigar. Then, just before I fell asleep at my keyboard, last night, It occurred to me that you may have posted an incorrect expected result. Thereby making it impossible to replicate.
Rows 15 :17 lead me to believe that you wanted first day of month followed by mid date + 1, for that month that is in B5:B9 In this case, 10/4/20
Then, further down the list, you have C26, 25/2/21 which you have not proceeded with the first day of that month!
Please clarify or correct me if I am wrong. Either way, I would welcome a food parcel and / or a pack of playing cards at this difficult time so I will PM, shortly, you with my postal address. ?

Stay Safe

 

[Snakehips]

I'm thinking tis is perhaps what you are wanting?

Book1
	A	B	C	D
2	Start date:	10/02/2020
3
4	Start	End	# Months
5	10/02/2020	09/04/2020	2
6	10/04/2020	24/02/2021	10.5
7	25/02/2021	24/02/2022	12
8	25/02/2022	24/02/2023	12
9	25/02/2023	09/10/2023	7.5
10
11
12			Period Start	Days in Period
13			10/02/2020	20
14			01/03/2020	31
15			01/04/2020	9
16			10/04/2020	21
17			01/05/2020	31
18			01/06/2020	30
19			01/07/2020	31
20			01/08/2020	31
21			01/09/2020	30
22			01/10/2020	31
23			01/11/2020	30
24			01/12/2020	31
25			01/01/2021	31
26			01/02/2021	24
27			25/02/2021	4
28			01/03/2021	31
29			01/04/2021	30
30			01/05/2021	31
31			01/06/2021	30
32			01/07/2021	31
33			01/08/2021	31
34			01/09/2021	30
35			01/10/2021	31
36			01/11/2021	30
37			01/12/2021	31
38			01/01/2022	31
39			01/02/2022	24
40			25/02/2022	4
41			01/03/2022	31
42			01/04/2022	30
COMBINED

Cell Formulas
Range	Formula
A5	A5	=B2
B5:B9	B5	=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9	A6	=B5+1
C13	C13	=A5
D13:D42	D13	=IF(ISBLANK(C14),B$9-C13,C14-C13)
C14:C42	C14	=IF(MATCH(C13,$A$5:$A$9,1)<>MATCH(EOMONTH(C13,0)+1,$A$5:$A$9,1),VLOOKUP(EOMONTH(C13,1),$A$5:$A$9,1,TRUE),EOMONTH(C13,0)+1)

Hope that helps

 

[Vkackley3300]

Tony, I owe you a million apologies!!! I did post an incorrect expected result. Thanks for your grace and for staying up all night and reading my mind. Please PM your address and I will send the requested food and cards. . Thanks for the formulas. I will check them out and report back. Something tells me they will be perfect for what I need. Apologies again. And thanks again.

 

[Vkackley3300]

Hi Tony. Thanks again for your help. I tested your formula and it seems to work great unless one of the inputs in c5:c9 is less than 2. This condition seems to create an error. For the life of me I cannot figure out to modify the formula to correct for this. In my example below you will see it does not pick up the date 11/1/2021 (November 1, 2021). Also, apologies for my US date formats.

Book2
	A	B	C	D
2	Start date:	10/2/2020
3
4	Start	End	# Months
5	10/2/2020	4/1/2021	6
6	4/2/2021	10/1/2021	6
7	10/2/2021	11/16/2021	1.5
8	11/17/2021	5/16/2022	6
9	5/17/2022	12/31/2022	7.5
10
11
12			Period Start	Days in Period
13			10/2/2020	30
14			11/1/2020	30
15			12/1/2020	31
16			1/1/2021	31
17			2/1/2021	28
18			3/1/2021	31
19			4/1/2021	1
20			4/2/2021	29
21			5/1/2021	31
22			6/1/2021	30
23			7/1/2021	31
24			8/1/2021	31
25			9/1/2021	30
26			10/1/2021	47
27			11/17/2021	14
28			12/1/2021	31
29			1/1/2022	31
30			2/1/2022	28
31			3/1/2022	31
32			4/1/2022	30
33			5/1/2022	16
34			5/17/2022	15
35			6/1/2022	30
36			7/1/2022	31
37			8/1/2022	31
38			9/1/2022	30
39			10/1/2022	31
40			11/1/2022	30
41			12/1/2022	31
42			1/1/2023	-1
Sheet1

Cell Formulas
Range	Formula
A5	A5	=B2
B5:B9	B5	=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9	A6	=B5+1
C13	C13	=A5
D13:D42	D13	=IF(ISBLANK(C14),B$9-C13,C14-C13)
C14:C42	C14	=IF(MATCH(C13,$A$5:$A$9,1)<>MATCH(EOMONTH(C13,0)+1,$A$5:$A$9,1),VLOOKUP(EOMONTH(C13,1),$A$5:$A$9,1,TRUE),EOMONTH(C13,0)+1)

 

[Snakehips]

Hi. I can honestly say that I do not properly understand what is happening there.
I can't really see a flaw in my basic logic although it's entirely possible. I'm tempted to put it down to a vagary of trying to do an approximate VLOOKUP. It would appear not to like the short period with consecutive months.
Any way, below, I have replaced the VLOOKUP , with INDEX / MATCH and, as far as my fuddled brain can tell, it appears to be ok.
Please do check it out for further flaws and if there are any more problems ...........

Book1
	A	B	C	D
4	Start	End	# Months
5	02/10/2020	01/04/2021	6
6	02/04/2021	01/10/2021	6
7	02/10/2021	16/11/2021	1.5
8	17/11/2021	16/05/2022	6
9	17/05/2022	31/12/2022	7.5
10
11
12			Period Start	Days in Period
13			02/10/2020	30
14			01/11/2020	30
15			01/12/2020	31
16			01/01/2021	31
17			01/02/2021	28
18			01/03/2021	31
19			01/04/2021	1
20			02/04/2021	29
21			01/05/2021	31
22			01/06/2021	30
23			01/07/2021	31
24			01/08/2021	31
25			01/09/2021	30
26			01/10/2021	1
27			02/10/2021	30
28			01/11/2021	16
29			17/11/2021	14
30			01/12/2021	31
31			01/01/2022	31
32			01/02/2022	28
33			01/03/2022	31
34			01/04/2022	30
35			01/05/2022	16
36			17/05/2022	15
37			01/06/2022	30
38			01/07/2022	31
39			01/08/2022	31
COMBINED

Cell Formulas
Range	Formula
A5	A5	=B2
B5:B9	B5	=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9	A6	=B5+1
C13	C13	=A5
D13:D39	D13	=IF(ISBLANK(C14),B$9-C13,C14-C13)
C14:C39	C14	=IF(MATCH(C13,$A$5:$A$9,1)<>MATCH(EOMONTH(C13,0)+1,$A$5:$A$9,1),INDEX($A$5:$A$9,MATCH(EOMONTH(C13,0)+1,$A$5:$A$9,1),1),EOMONTH(C13,0)+1)