Need help with some date formulas

Vkackley3300

New Member
Joined
Apr 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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)
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,122
Office Version
  1. 2013
Platform
  1. Windows
@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
Joined
May 17, 2009
Messages
5,122
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Apr 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 27, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 17, 2009
Messages
5,122
Office Version
  1. 2013
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,751
Messages
5,626,650
Members
416,198
Latest member
Enigma909

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