+ Reply to Thread
Results 1 to 7 of 7

Date range selector fails to work

  1. #1
    Forum Contributor
    Join Date
    02-09-2007
    Location
    UK
    MS-Off Ver
    2016
    Posts
    219

    Date range selector fails to work

    Hello all,

    Using SUMIFS, the basic one criteria works correctly, but when I try to use a date range selector as criteria 2 and 3, if fails.

    I cant work out why.

    =SUMIFS(Data!H:H,Data!C:C,A2,Data!M:M, ">="&H1,Data!M:M, "<="&H2)

    Thanks BVG
    Attached Files Attached Files
    Last edited by Badvgood; 03-26-2020 at 04:09 PM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    6,447

    Re: Date range selector fails to work

    The "dates" in column M are text not real dates. The best option would be to convert them to real dates

  3. #3
    Forum Contributor
    Join Date
    02-09-2007
    Location
    UK
    MS-Off Ver
    2016
    Posts
    219

    Re: Date range selector fails to work

    Quote Originally Posted by Fluff13 View Post
    The "dates" in column M are text not real dates. The best option would be to convert them to real dates
    Thanks, I had checked this but may be incorrect. In column N, I used the formula =M1+1 to check this and the date increased by 1. Is this not a way of checking that dates are stored as dates?

    Thanks BVG

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,991

    Re: Date range selector fails to work

    Try this in N2 >> =ISNUMBER(M2)

    You will get FALSE which means M2 is not a number
    HTH
    Regards, Jeff

  5. #5
    Forum Contributor
    Join Date
    02-09-2007
    Location
    UK
    MS-Off Ver
    2016
    Posts
    219

    Re: Date range selector fails to work

    Quote Originally Posted by jeffreybrown View Post
    Try this in N2 >> =ISNUMBER(M2)

    You will get FALSE which means M2 is not a number
    Thanks Jeff, very useful to know. Can I ask what you think is the best way for me to convert all the 'dates' is to resolve my issue?

    Thanks BVG

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,991

    Re: Date range selector fails to work

    Try this

    Convert Range of text to numbers
    Data >> Data Tools >> Text to Columns
    Highlight the range to convert >> All of column M
    Next >> Next >> Date >> DMY >> Finish
    Last edited by jeffreybrown; 03-26-2020 at 03:10 PM.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    6,447

    Re: Date range selector fails to work

    Best way to check dates is select the column & change the format to general, if you a number like 43861 it's a date, if you see 31/01/2020 it's text

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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