Need help with some date formulas

Vkackley3300

New Member
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.

Cell Formulas
RangeFormula
A5A5=B2
B5:B9B5=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9A6=B5+1
C13C13=A5
C53:C60,C40:C51,C27:C38,C17:C25,C14:C15C14=DATE(YEAR(C13),MONTH(C13)+1,1)
D13:D60D13=IF(ISBLANK(C14),B\$9-C13,C14-C13)

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Snakehips

Well-known Member
@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

Well-known Member
I'm thinking tis is perhaps what you are wanting?
Cell Formulas
RangeFormula
A5A5=B2
B5:B9B5=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9A6=B5+1
C13C13=A5
D13:D42D13=IF(ISBLANK(C14),B\$9-C13,C14-C13)
C14:C42C14=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

New Member
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

New Member
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.

Cell Formulas
RangeFormula
A5A5=B2
B5:B9B5=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9A6=B5+1
C13C13=A5
D13:D42D13=IF(ISBLANK(C14),B\$9-C13,C14-C13)
C14:C42C14=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

Well-known Member
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 ...........

Cell Formulas
RangeFormula
A5A5=B2
B5:B9B5=EDATE(A5,C5)+MOD(C5,1)*30-1
A6:A9A6=B5+1
C13C13=A5
D13:D39D13=IF(ISBLANK(C14),B\$9-C13,C14-C13)
C14:C39C14=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)

Replies
8
Views
179
Replies
2
Views
74
Replies
1
Views
60
Replies
20
Views
423
Replies
9
Views
78

1,127,913
Messages
5,627,601
Members
416,255
Latest member
amethystia

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.

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

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