1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by inddon, Nov 7, 2018.

  1. inddon

    inddon Member

    Messages:
    459
    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

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    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?
  3. inddon

    inddon Member

    Messages:
    459

    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
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,005
    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

    Attached Files:

    inddon likes this.
  5. inddon

    inddon Member

    Messages:
    459
    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

Share This Page