+ Reply to Thread
Results 1 to 4 of 4

index, match splitting two dates range by months in columns

  1. #1
    Registered User
    Join Date
    01-03-2016
    Location
    Serbia
    MS-Off Ver
    2007
    Posts
    25

    index, match splitting two dates range by months in columns

    Hy,

    I have names in the rows and 4 periods (dates representing max 4 parts of yearly vacation) in columns
    need to convert and split that 4 periods (beginning date and ending date * 4) by monthly columns...

    A B C D E F G H I

    name1 - 03.03.2018 - 20.03.2018 - empty - empty - 20.04.2018 - 05.05.2018 - empty - empty -
    name2 - 25.03.2018 - 20.04.2018 - empty - empty - 20.06.2018 - 25.06.2018 - empty - empty -
    name3 - 03.03.2018 - 20.03.2018 - empty - empty - 20.04.2018 - 05.05.2018 - empty - empty -

    result

    ... feb mar apr may jun jul ... (month and year)

    name1 - 03-20.03 20-30.04 01-05.05
    name2 - 25-31.03 01-20.04 20-25.06
    name3 - ....

    Is there any way to do this?

    i tried
    =IF(MONTH($B2)=MONTH($I$1);INDEX($B$2:$C$4;MATCH(H2;$A$2:$A$4;0);1);"")
    and
    A1
    =IF(MONTH($B1)=MONTH($A1);B1;EOMONTH($A1;0))
    =IF(MONTH($B1)=MONTH($A1);"";EOMONTH($A1;0)+1)
    =IF(MONTH($B1)=MONTH($A1);"";$B1)
    then
    =IFERROR(IF(MONTH(D1)=MONTH($K$10);D1;IF(MONTH(H1)=MONTH($K$10);H1;""));"")
    =IFERROR(IF(MONTH(F1)=MONTH($M$10);F1;IF(MONTH(J1)=MONTH($M$10);J1;""));"")

    but I cant put it all togeather

    Thanks

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: index, match splitting two dates range by months in columns

    in excel file it would be easier to get the picture

  3. #3
    Registered User
    Join Date
    01-03-2016
    Location
    Serbia
    MS-Off Ver
    2007
    Posts
    25

    Re: index, match splitting two dates range by months in columns

    From Sheet ALL
    to Sheet DISTRIBUTION
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-03-2016
    Location
    Serbia
    MS-Off Ver
    2007
    Posts
    25

    Re: index, match splitting two dates range by months in columns

    Help anyone

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] compare dates and match months intervals
    By thedrinkerparadox in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-16-2017, 12:53 PM
  2. Index Match to find match date to a range of dates
    By downcrusher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2017, 06:05 PM
  3. [SOLVED] Index match if "date" is between 2 dates in 2 seperate columns.
    By Benjiboi_87 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 04-28-2016, 02:01 AM
  4. INDEX/MATCH Formula issue-comparing dates in 4 columns to return a value
    By hopegriffin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-06-2016, 05:15 PM
  5. Index Match based on months away from current month
    By lpuerta75 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-15-2014, 04:24 PM
  6. [SOLVED] Splitting single column of dates to 2 columns representing a beginning and end date
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-21-2013, 11:16 PM
  7. [SOLVED] Sum function with match months and add columns
    By sureshpunna in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-23-2013, 05:54 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1