23 responses

  1. Pati Giffney
    June 15, 2009

    I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09
    Cell B1 1/11/09-1/17/09
    Cell C1 1/18/09-1/24/09

    How do I create a formula to do this?

    Reply

  2. admin
    June 16, 2009
  3. Eddie G
    June 25, 2012

    I have a very similar problem with dates but my dates are in a matrix (an array). So columns A through J (A2:J60) have dates and I want to put the entire range of dates into one column say column A in sheet 2 but I want them in ascending order. Any chance you can help with that.

    Kind Thanks,
    Eddie

    Reply

  4. Oscar
    July 2, 2012

    Eddie G,

    Array formula in cell A1, sheet2:

    =SMALL(Sheet1!$A$2:$J$60, ROW(A1))

    Thanks for bringing this post to my attention. I have now simplified the formula in this post.

    Reply

  5. Ed
    July 2, 2013

    I’m using a formula to get a report that bring me the total persons from a specific colum in a determinated date:

    {SUM(IF((DATE>=FirstDate)*(DATE<=LastDate)*(TYPE=TypeName)*(SUBTYPE=SubtypeName),NameColum2,0))}

    When I want to get the result from the "Name Colum2" it works well, but when I change the colum ("NameColum3") it appears an #NAME? error.

    I have an excel spreadsheet that has different colums:
    TYPE,SUBTYPE,DATE,NameColum2,NameColum3,etc.
    Could you help me?
    Thanks
    Ed

    Reply

  6. Juliana
    August 17, 2013

    Hi I am trying to apply this formula to a search I need to run. I have 4 types of searches, in a dropdown box with conditional formatting showing the user where to enter the specific data that needs to be searched for. I have been able to complete two the search if statements (bill number and file number) however I can not complete the other two which is invoice number and date range.

    I would like the formula to always return the applicable bill num if the start and end date of the invoice falls in the specified range .

    the formula is getting so long that I am lost. here is the link …
    https://www.dropbox.com/s/5o3n2kln2u8v7yn/invoice%20for%20lily%20%28Autosaved%29.xlsx

    many thanks in advance …

    Reply

  7. La Thăng
    August 4, 2015

    Hi Pro, i want to find day if given date and week, for example : if given Tuesday, 32th week, 2015 >>> how to use function to point out 4/8/2015 ?

    Reply

  8. Adran
    January 19, 2016

    If I want to search a certain criteria in two dates in above example. For an example, machine A within two dates.How I do it?

    Reply

  9. Milos
    May 3, 2016

    Pls help

    i have A1 to A300 employee names
    B1 to B300 start date of vacation
    C1 to C300 end date of vacation

    need to count how many employees are absent from work for each day in a year to create a chart (ex: 05.05.2016. 55 are absent, 06.05.2016. 59 are absent… and so on)

    and, if its possible to make list of their names… but that is a separate question.

    Reply

  10. Rob
    November 16, 2016

    Hi Oscar
    Really love your website. Its very useful. I’m using this formula but need help. I have a table with Dates, Codes, Description etc. These table is used to input data which may or may not be in date order.

    I then need to copy this data into another area but I want it sorted by date order, which I have successfully done. However, I also want the Codes, Description etc linked to the date to also come across to the sorted date formula used above.

    Is this possible?

    Reply

  11. Lokesh
    May 3, 2017

    Hi,
    What I have to take on the place of list in this formula??
    Pls Help

    Reply

    • Lokesh
      May 3, 2017

      I am getting Num! Error in this Date Sorting Formula

      Reply

    • Oscar
      May 3, 2017

      Did you enter it as an array formula?

      Reply

  12. Lokesh
    May 3, 2017

    I have used in this way

    =SMALL(IF((K5:K24=$L$1),K5:K24,””),K5:K24)

    Reply

    • Oscar
      May 3, 2017

      Lokesh

      You need to use absolute cell refs except the last one, like this:

      =SMALL(IF(($A$1:$A$10< =$E$2)*($A$1:$A$10>=$E$1), List, “”), ROW(A1))

      Row(A1) returns a number that changes when you copy/paste the formula to cells below. That way you can get the smallest value and then the second smallest value and so on.

      Reply

  13. Lokesh
    May 3, 2017

    Hello Sir,
    I have Got the Answer but I didn’t understand the presence of Row(A1)in this Formula. Could u pls help me understand that

    Reply

  14. La Thăng
    August 7, 2015

    Thanks so much Pro :) if you have chance to VietNam visit, pls tell me know ^^

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top