+ Reply to Thread
Results 1 to 6 of 6

How do I convert a table with individual dates to a table with date ranges?

  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    How do I convert a table with individual dates to a table with date ranges?

    A sample file is attached. On the "Basic table" sheet, there is a basic table with the following columns: ID, Name, Start of leave, End of leave. I need to convert this table to a table that looks like the one on the "New table" sheet.
    As you can see, the data is displayed by date ranges (Jan - Apr (Range1), May - Aug (Range2), Sep - Dec (Range3)). I used the following formula to prepare this table:

    Please Login or Register  to view this content.
    ,

    wherein XX represents the corresponding numerical designation of the named range, e.g. StartD01, StartD02, etc.

    The problem occurs when the start date is in one date range (eg in date range 2 (May - Aug)), and the end date is in another date range (eg in date range 3 (Sep - Dec (Range3)). In this case, the formula does not return anything (which is, of course, correct according to the written formula), and I have to enter the relevant data manually (see ID 29 on the "New table" sheet - marked in red).

    I would like to know if there is any better way to prepare the data as shown in the new table?

    Could I somehow correct the formula so that even in the cases described above, the data will be displayed correctly, as in the case of a marked record with ID 29?

    Would it be possible to prepare such a table also using a Pivot Table?
    Attached Files Attached Files
    Last edited by frankt68; 03-25-2021 at 07:09 AM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How do I convert a table with individual dates to a table with date ranges?

    Hi,
    Are you dealing with multiple years or just 2021?

  3. #3
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: How do I convert a table with individual dates to a table with date ranges?

    Hi!

    Currently just with 2021, but I guess in the future I'll have to do it for multiple years.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: How do I convert a table with individual dates to a table with date ranges?

    c2=IF(AND('Basic table'!$D2>=StartD01,'Basic table'!$C2<=EndD01),CONCATENATE(TEXT(MAX(StartD01,'Basic table'!C2),"dd.mm.yyyy")," - ",TEXT(MIN(EndD01,'Basic table'!D2),"dd.mm.yyyy")),"")

    d2=IF(AND('Basic table'!$D2>=StartD02,'Basic table'!$C2<=EndD02),CONCATENATE(TEXT(MAX(StartD02,'Basic table'!C2),"dd.mm.yyyy")," - ",TEXT(MIN(EndD02,'Basic table'!D2),"dd.mm.yyyy")),"")

    e2=IF(AND('Basic table'!$D2>=StartD03,'Basic table'!$C2<=EndD03),CONCATENATE(TEXT(MAX(StartD03,'Basic table'!C2),"dd.mm.yyyy")," - ",TEXT(MIN(EndD03,'Basic table'!D2),"dd.mm.yyyy")),"")

    try the above, copy and paste towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How do I convert a table with individual dates to a table with date ranges?

    ---Deleted-

  6. #6
    Registered User
    Join Date
    05-07-2013
    Location
    Celje, Slovenia
    MS-Off Ver
    2016, 365
    Posts
    29

    Re: How do I convert a table with individual dates to a table with date ranges?

    Hi samba-ravi!

    Great solution, works like a charm...

    Thanks...
    Last edited by frankt68; 03-29-2021 at 09:01 AM.

+ 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. Replies: 1
    Last Post: 09-21-2020, 07:35 AM
  2. Convert calendar formula from individual dates to date range & show overlap
    By agb02917 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2020, 10:54 AM
  3. Replies: 3
    Last Post: 05-06-2020, 12:29 PM
  4. Checking whether multiple individual dates fall within any date ranges
    By AbuZabi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2016, 04:37 PM
  5. Why my Pivot Table Filter shows individual dates
    By chico.corrales in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-28-2016, 05:52 AM
  6. Replies: 4
    Last Post: 10-05-2012, 03:06 PM
  7. Pivot Table - Group Individual Dates Into Months
    By Kolin in forum Excel General
    Replies: 0
    Last Post: 07-15-2010, 11:02 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