• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

I

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 have uploaded a sample workbook for your reference.

Could you please advise how this can be achieved?

I am trying to resolve my earlier post by creating a helper columns as above.
Post is: https://chandoo.org/forum/threads/n...r-presence-work-presence-across-months.40196/

Appreciate your help on both the posts

Many thanks & regards,
Don
 

Attachments

  • Sample Generate Date month wise based on a Date Range.xlsx
    11 KB · Views: 5
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?
 
GraH - Guido said:
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?
Click to expand...


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

  • Generate Date month wise based on a Date Range(1).xlsx
    12.3 KB · Views: 11
Hi Bosco,

Thanks a ton. It works so wonderful. :awesome::awesome::awesome::)

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
 
You must log in or register to reply here.
Back
Top