+ Reply to Thread
Results 1 to 4 of 4

Finding a function to help me sort dates

  1. #1
    Registered User
    Join Date
    09-17-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    14

    Finding a function to help me sort dates

    I posted recently looking for a solution to a date formatting problem, but after some time decided it would be easier to just reformat each date individually and deal with it from there.

    I have a large list of trades from my companies trade log that includes delivery periods. The periods are identified by a column of start dates, and then a second column of end dates. I want to be able to consolidate all trades that include a delivery period that falls within a certain month. Here's an example:

    Nov 2014 (The month that I'm trying to pull trades for)
    (I want to have the data populate below, having pulled the entire rows that include the correct delivery periods)
    Delivery Start////////// Delivery End
    1 Jan 2014//////////// 31 Dec 2014 blah blah blah trade info
    1 Oct 2014/////////// 31 Dec 2014 blah blah blah trade info
    1 Nov 2014////////// 30 Nov 2015 blah blah blah trade info
    etc...
    etc...

    Basically any delivery period that INCLUDES the month of Nov 2014, would be pulled, including the rest of the row. Anyone have a solution here?

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,298

    Re: Finding a function to help me sort dates

    Use a column of formulas like

    =AND(A2<DATE(YEAR($E$1),MONTH($E$1)+1,1),Z2>DATE(YEAR($E$1),MONTH($E$1),0))

    Where A has your Delivery Start dates, Z has your Delivery End dates, and E1 has a date from the month of interest, like 11/1/2014.

    Copy down to match your data, then filter the data set based on the column of formulas being TRUE.

    If you truly want to consolidate your data, I would recommend using a pivot table, which can use all sorts of filtering and summing/counting/averaging functions on your data set, with no formulas required.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-17-2014
    Location
    United States
    MS-Off Ver
    2010
    Posts
    14

    Re: Finding a function to help me sort dates

    Wonderful, of course. I only have one other question, how can I count up the amount of months between 2 dates?

    So using whatever function, if i had two dates 1 Nov 2014, and 31 Mar 2015, it would return the number 5, representing nov, dec, jan, feb, and mar. Any ideas?

    Also I'll take a look at using the pivot tables as well, I just need to be able to copy and paste out the data when I begin sorting by each month

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,298

    Re: Finding a function to help me sort dates

    =DATEDIF("1 Nov 2014", "31 Mar 2015", "m")

    will return 4, the number of complete months between the two dates, and

    =DATEDIF("1 Nov 2014", "31 Mar 2015", "m") +1

    will return 5, the number of partial months.

    The DATEDIF function is the least-documented common function in Excel, not even appearing in Excel help, so visit:

    http://www.cpearson.com/excel/datedif.aspx

+ 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. Sort receipts based on their dates - Problem with a nested function.
    By gubiithefish in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-21-2014, 04:42 PM
  2. Finding Missing Dates In a Column of Dates
    By ExcelStudent123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2014, 05:37 PM
  3. Replies: 4
    Last Post: 12-19-2011, 08:40 AM
  4. [SOLVED] finding last row in a fixed section then sort
    By toolman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-16-2006, 06:50 PM
  5. Replies: 1
    Last Post: 06-22-2005, 10:05 PM

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