Comparing Dates

Follow

Krishna Bakka

• July 08, 2016 18:30

Hi All,

I have Converted date in a column as a string like 03/10/2015 and I would like to compare this field with a manual date(Ex: 03/28/2015) in formulas. I am not getting 100% correct results few of the records are missing. 

Ex : IF(GT(#text_create_date;"03/28/2015");"Complete";"Not Complete")

few of the records are not giving correct results Ex : for 03/01/2016 text_create_date it is throwing me "Not Complete" results. 

 

Please help me on this.

0

• 

  Joel Stewart

  • July 08, 2016 19:03

  I recommend doing any date-type comparisons as DATE objects rather than STRING objects. Strings require that the format of the date is in descending order of magnitude for calendar values (year, month, day) and ensure that leading-zeros are used too.

  The example you shared is a good example of why a month-day-year organization in a string will not produce the correct results:

  • "03/01/2016" is not greater than "03/28/2015" - because "0" is not greater than "2" from the first digit of the days section

  You have two options to correct for this: 

  1. Reformat the date to be yyyyMMdd (descending in magnitude). This ensures that "20160301" is greater than "20150328" because "6" is greater than "5" in the last digit of the year. 
  2. OR, convert the Strings to Date objects in Datameer. Datameer will automatically compare them based on the calendar date for each entry

  1

  Comment actions Permalink
• 

  Krishna Bakka

  • July 11, 2016 11:19

  Thank you so much.. It is working for me..

  0

  Comment actions Permalink

Please sign in to leave a comment.