Skip to main content
Solved

Date comparison not working only in one instance?

  • May 22, 2025
  • 3 replies
  • 70 views
A
Forum|alt.badge.img+9

Hi,

I’m trying to create a field that tells you when the next due date of your credit card payments are, based on what their monthly due date is (e.g. 5th of every month) and today’s date. I know I’m creating a string that looks like a date and not a date itself, which isn’t a problem for me.

My issue is that the comparison I’m using to check if the Payment Due Date is before today, a simple comparison of values really, is returning one wrong result! I created extra formula fields to see where things went wrong, and I cannot for the life of me figure out what the issue is! 

Basically, when I compare the number in Today against the Payment Due Date, with this formula: IF(DATETIME_FORMAT(TODAY(), "DD") > {Payment Due Date}, "yes", "no")  I get an incorrect result (Payment Due Date 5 returns “no” when it should be “yes,” because 22>5). I don’t get it. Does anyone know what’s going on?

 

Thanks a lot!

 

 

Best answer by tahajiru

You're very close. Your Payment Due Date field is set as text instead of a number, which causes a lexicographical comparison rather than a numerical one.

For example:
"22" > "5" → false
"22" > "05" → true

The fix is to change the field type to number.

Taha, Airtable Advisor

    3 replies

    T
    Forum|alt.badge.img+4
    • tahajiru
    • Participating Frequently
    • Answer
    • May 22, 2025

    You're very close. Your Payment Due Date field is set as text instead of a number, which causes a lexicographical comparison rather than a numerical one.

    For example:
    "22" > "5" → false
    "22" > "05" → true

    The fix is to change the field type to number.

    Taha, Airtable Advisor

    TheTimeSavingCo
    Forum|alt.badge.img+31

    Does this look right?

    IF(
      DATETIME_FORMAT(TODAY(), "DD") > VALUE({Payment due date}), 
      "yes", "no"
    )

     

    Adam, Airtable Consultant - thetimesaving.company
    A
    Forum|alt.badge.img+9
    • annebocc
    • Author
    • Inspiring
    • May 22, 2025

    Thank you ​@tahajiru, I changed the field type to a number field and it all works beautifully! 

    ​@TheTimeSavingCo , I tried your solution also and it worked as well. Thank you both so much – I knew it had to be something simple that I was missing.

     

    Terms & ConditionsAccessibility statement