+ Reply to Thread
Results 1 to 10 of 10

Comparing Dates In a Logical Formula (Workaround)

  1. #1
    Registered User
    Join Date
    06-18-2019
    Location
    Seattle, WA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    Comparing Dates In a Logical Formula (Workaround)

    Just wanted to post this in case any poor soul runs into the same issue as I did and can't find a solution.

    For some reason Excel doesn't like it when you type dates into a formula. I have a table where I'd like to return a few values based on the dates in an adjacent column. To do this, I used an IFS formula,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . For some reason, this will always evaluate the first logical test:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    as true, no matter what date is in C14.

    I tried putting the dates in quotes so the equation read
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . But then it would just evaluate everything as if the second logical was true. (Ie. It put "Winter 2019" for everything.

    I found a workaround was to put the dates in their own cells, and reference the cell, as opposed to typing the date in manually. So the formula now read
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    where the value in E5 was 3/1/2019 and F5 was 7/1/2019.

    Similarly, if the dates are written inside the formula with the DATE function, it works as well.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Comparing Dates In a Logical Formula (Workaround)

    That is not a date, it is a numeric expression. You are comparing C14 to 7 divided by 1 divided by 2019, which is some small number less than 1. C14 as date will always be greater than 1, hence False.

    DATE is the best way to go IMO< but you can hardcode the date string and compare, like so

    C14<--"2019-07-01"

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,186

    Re: Comparing Dates In a Logical Formula (Workaround)

    Using DATE is the way to go as it will avoid any issues/confusion with Regional Settings.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Comparing Dates In a Logical Formula (Workaround)

    Quote Originally Posted by TMS View Post
    Using DATE is the way to go as it will avoid any issues/confusion with Regional Settings.
    so does --"2019-07-01"

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,186

    Re: Comparing Dates In a Logical Formula (Workaround)

    Fair enough, but maybe less intuitive?

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Comparing Dates In a Logical Formula (Workaround)

    I'm with Trevor on this one, Bob!

    Less experience users might not know if --"2019-07-01" was July 1st or January 7th so might it has potential to cause confusion, at least the DATE function promts you to enter it in the correct order.

    Most people will enter a date in their preferred format, dd/mm/yy in the UK, mm/dd/yy in the US, --"01-07-2019" will return the correct date in the UK, but what if the file is sent to a colleague in the US? As the date is a text string, it will not be adapted to regional settings, so now the formula will give incorect results.

    @Cryptikfox, your formula has a hole in the results, dates from 3/1/19 and 7/1/19 will return #N/A

    For what it appears that you are tryng to do, I would consider a formula on the basis of
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    but with the hole in the results it is not clear exactly where each season should start and end.

  7. #7
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Comparing Dates In a Logical Formula (Workaround)

    Quote Originally Posted by TMS View Post
    Fair enough, but maybe less intuitive?
    Far less intuitive . I agree that DATE is the best way, I said so in my reply, but as always with Excel, that poor old cat can be skinned many ways.

  8. #8
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Comparing Dates In a Logical Formula (Workaround)

    Quote Originally Posted by jason.b75 View Post
    Less experience users might not know if --"2019-07-01" was July 1st or January 7th ...
    Not disagreeing that DATE is better, but totally disagree with you on this point. yyyy-mm-dd is an ISO standard, recognised across the world. I understand that some parts of the world confusingly see a date of 07/01/2019 as 1st July, but nobody sees 2019-07-01 as 7th Jan.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Comparing Dates In a Logical Formula (Workaround)

    Quote Originally Posted by Bob Phillips View Post
    but nobody sees 2019-07-01 as 7th Jan.
    If nobody sees it, then how did I see it to comment on it? Someone working for ms must have seen it as well, otherwise YDM wouldn't be allowed for in the text to columns date format options.

    Even if it is an ISO standard, you shouldn't assume that everybody knows it. Outside of the forum, it's a standard that I've never encountered or had cause to use.

    Given how many threads we see that are far from standard...

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Comparing Dates In a Logical Formula (Workaround)

    Apologies if this sounds like I'm trying to prove you wrong, Bob. Curiosity was getting the better of me, so I looked into it a bit more.

    Following up on the last couple of posts, found this on wikipedia

    Gregorian, year-day-month (YDM)
    This date format is used in Kazakhstan, Latvia, Nepal, and Turkmenistan. According to the official rules of documenting dates by governmental authorities,[3] the date format in Kazakh is written as YYYY.DD.MM, e.g. 2006.05.04 or 2006 5 April (Kazakh: 2006 жылғы 05 сәуір)

+ 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. 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
  2. [SOLVED] formula not comparing dates properly
    By johnandrews in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-16-2013, 12:44 PM
  3. [SOLVED] If statment comparing dates then calculating formula - please help
    By tcrjmom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2013, 05:39 AM
  4. Conditional Formatting (or a formula) For Dates Comparing to the Current Date
    By shoebox54th in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2013, 01:20 PM
  5. [SOLVED] Conditional Formatting or formula for comparing dates
    By sweetds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2013, 10:34 AM
  6. [SOLVED] comparing dates using a formula
    By coronas in forum Excel General
    Replies: 6
    Last Post: 06-08-2012, 01:38 PM
  7. Replies: 1
    Last Post: 10-07-2011, 10:26 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