# Need a formula to Generate month wise date, based on a Date Range

#### inddon

##### Member
Hi There,

I have a date range (From and To Date) and month columns from Jan to Dec.
Based on the From and To Date, it should generate the date for the month (Jan to Dec) column.

Eg.
From Date: 15-Feb-2018
To Date: 30-Apr-2018

Under Column Jan it would be empty
Under column Feb it should show 15-Feb-2018
Under column Mar it should show 31-Mar-2018
Under column Apr it should show 30-Apr-2018
(If To Date is 15-Apr-2018, then it should show 15-Apr-2018)

I am trying to resolve my earlier post by creating a helper columns as above.

Appreciate your help on both the posts

Many thanks & regards,
Don

#### Attachments

• 11 KB Views: 3

#### GraH - Guido

##### Well-Known Member
Why is 31/01 the result for the first row in the first column and 15/02 in the second row/column and not end of month 28/02?

#### inddon

##### Member
Why is 31/01 the result for the first row in the first column and 15/02 in the second row/column and not end of month 28/02?

Hi Grah,

Thank you for your reply. The person's availability is calculated based on the From and To date precisely.

If it is a whole month, then the end of month date should be displayed.

If it is not a whole month in the from or to date, then in under it's respective month column it should display the end of that month rather what is displayed in the from or to date.

eg. 10-Feb, it should show 10-Feb (not 28-Feb)
27-Feb, then it should show 27-Feb (not 28-Feb)
01-Feb then it should display 28-Feb

Regards,
Don

#### bosco_yip

##### Excel Ninja
Try................

In D8, copied across and down :

=IFERROR(IF((TEXT(\$B8,"mmm")=D\$2)*(DAY(\$B8)>1),\$B8,--(MAX(0,MIN(EOMONTH(--(1&D\$2),0),IF(\$C8="",TODAY(),\$C8))-MAX(--(1&D\$2),\$B8)+1)&D\$2)),"")

Regards
Bosco

#### Attachments

• 12.3 KB Views: 5

#### inddon

##### Member
Hi Bosco,

Thanks a ton. It works so wonderful.

I was trying to debug your formula and would like to know what each part does. When you have some free time to spare, would appreciate if you could explain the parts in the formula.

The characters '--' you have used is very new and interesting to me. I am curious to know about the explanation of the formula.

Many thanks again and look forward to hearing from you.

Regards,
Don