Results 1 to 4 of 4
  1. #1

    Default comparing dates in excel

    Dear god I need some help on this, this is doing my head in....

    I have extensively googled this and tried various approaches to no avail.

    I have a column of dates in general format, I want to compare them with todays date say, if its greater than returns 1, less than returns 0.

    the latest attempt:
    =IF(G2>AB2,"y","n")
    just returns 'n' no matter what I make AB2

    If I try and change the cells to Date format it then just returns a "Y'.

    I dunno what I'm doing wrong.

    The date column is imported from another system thats why its in general format as 02.03.2011.

    would really appreciate any help.
    Thanks!

  2. #2
    Network Engineer SolMiester's Avatar
    Join Date
    Feb 2005
    Location
    Napier
    Posts
    8,184

    Default Re: comparing dates in excel

    Try putting the format into a proper excel formula =date(yyyy.mm,dd)
    HOME-LianLi PC-9F,ASRock P67Pro3, i5 2500k @4Ghz, 8Gb HyperX, ASUS GTX660 OC, Corsair Force 120 SSD, HP zIPS22", HOME SERVER HP ML110G6 HOST-Plex\Ubuntu\8

  3. #3
    Junior Member ManUFan's Avatar
    Join Date
    Feb 2006
    Location
    Greymouth
    Posts
    226

    Default Re: comparing dates in excel

    Try a find & replace (CTRL + H) on the 02.02.2011 etc (replace . with / to give a 'proper' date format) becomes 02/02/2011.

    This worked for me. Would not work with the date format you have (02.02.2011).

    Formula I used was: =if(A1>today(),"yes","no") where A1 contains the date to compare to today.

  4. #4
    Senior Member MushHead's Avatar
    Join Date
    Jun 2006
    Posts
    492

    Default Re: comparing dates in excel

    If you have no control over the imported text & you have to re-import it (so doing the Ctrl+H thing every time is impractical), you can dynamically convert to date format before doing the comparison using the formula:

    DATEVALUE(SUBSTITUTE(A1,".","/"))

    So in your example, you'd end up with

    =IF(DATEVALUE(SUBSTITUTE(G2,".","/"))>AB2,"y","n")

    (assuming G2 was your imported date)
    Last edited by MushHead; 17-06-2011 at 04:57 PM.
    There are 10 types of people in this world - those who understand binary, and those who don't.

Similar Threads

  1. OT - entering dates into Excel
    By annie in forum PressF1
    Replies: 4
    Last Post: 21-11-2004, 04:27 PM
  2. Dates in Excel
    By Captn Andy in forum PressF1
    Replies: 4
    Last Post: 03-08-2003, 11:15 PM
  3. Sorting Dates in Excel
    By Muzz in forum PressF1
    Replies: 13
    Last Post: 07-04-2003, 10:50 PM
  4. Excel - Combo Box & Dates
    By in forum PressF1
    Replies: 1
    Last Post: 10-06-2002, 01:31 PM
  5. Excel VBA Dates
    By in forum PressF1
    Replies: 0
    Last Post: 06-09-2000, 07:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •