Skip to main content
Solved

Nested IF Formula for dates, 3 possible responses, incl. blank?

  • March 20, 2020
  • 7 replies
  • 62 views
E

Dear Airtablers,

I fully accept that I might be working non-intuitively, and am looking for help and suggestions.

I‘m attempting to keep my accounts under control and wish to flag up the 4 possible statuses I can conceive - either an invoice is „Paid on time“, „Paid late“, „Open“ (unpaid but not yet late) or the worst kind „Unpaid and late“.

As a beginner I‘m having terrible trouble but not much fun finding out how to do it.

I have the following fields:

  • Date invoice sent
  • Terms in days
  • Due date (automatically generated from formula)
  • Date paid (blank if open)
  • Pay cycle (in how many days did my client pay?)
  • Days late
  • Punctual/late/unpaid?

What I (think I) want:

In „days late“, I want to show 3 things.

  1. Invoice on time (thumbs up)
  2. Invoice late and unpaid (exclamation mark and number of late days)
  3. Invoice late and paid (alarm clock and number of days late)

So far I managed:

IF({Pay cycle in days} >14, DATETIME_DIFF({Date paid},{Due date},"days"))

How do I nest the next two settings, and what order should they go in?

Thanks in advance for helping me with my baby steps.

Best answer by Mohamed_Swella1

Hello Again!

Nearly there!

  1. Unpaid late invoices are marked with „on time“, not „x days late“ (in the field „days late“ there is no entry

  2. it’d be nice to have the text say „ :alarm_clock: /!! x days late“ - can I just change:

    “:alarm_clock:”&{Days Late}

To

‚„:alarm_clock: &{Days Late} days late“‘

?

If it helps, in my days late field I have:

IF({Pay cycle in days} >14, DATETIME_DIFF({Date paid},{Due date},"days"))

Can you tell I‘m new at this?


Hi Edd,

  1. The Days late has to be a formula , check the first formula.

  2. You can add &" Days Late" to both strings so it would read what you want.

We were all new to this one day, one step at a time :slightly_smiling_face: feel free to ask as many questions as you need

7 replies

Mohamed_Swella1
Forum|alt.badge.img+17

Hi @Edd_Turner,

Welcome to Airtable Community ! :grinning_face_with_big_eyes:

If I understand correctly, Pay Cycle in days already have the difference between Due date and Date paid, correct?

I assume that is the formula for the days late

IF({Date paid}=BLANK(),DATETIME_DIFF(TODAY(),{Due date}, 'days'), DATETIME_DIFF({Date paid},{Due date}, 'days'))

Then the formula that you are looking for should be

IF({Days Late} <= 0, "Invoice on time 👍", IF(AND({Due date} < TODAY(),{Date paid}=BLANK()), "‼️"&{Days Late},"⏰"&{Days Late}))

This formula does the following:

  1. If the invoice is paid on time or before its due time, it returns “Invoice on time :thumbs_up: ”
  2. If the invoice is not yet paid AND the due date has passed, it returns “ :bangbang: ”&{Days Late}
  3. If the invoice is paid but the paid date is after the due date, it returns “ :alarm_clock: ”&{Days Late}

I believe this is what you were looking for?

BR,
Mo

E
  • Edd_Turner
  • Author
  • Known Participant
  • March 20, 2020

Hello Mo!

Thanks very much for spending the time to decipher my somewhat convoluted question.

I‘ll take a look later and see if…

A) I managed to phrase my question right and
B) If your kindly offered code works

Stay healthy!

Thanks

Edd

Mohamed_Swella1
E
  • Edd_Turner
  • Author
  • Known Participant
  • March 20, 2020

Hi @Edd_Turner,

Welcome to Airtable Community ! :grinning_face_with_big_eyes:

If I understand correctly, Pay Cycle in days already have the difference between Due date and Date paid, correct?

I assume that is the formula for the days late

IF({Date paid}=BLANK(),DATETIME_DIFF(TODAY(),{Due date}, 'days'), DATETIME_DIFF({Date paid},{Due date}, 'days'))

Then the formula that you are looking for should be

IF({Days Late} <= 0, "Invoice on time 👍", IF(AND({Due date} < TODAY(),{Date paid}=BLANK()), "‼️"&{Days Late},"⏰"&{Days Late}))

This formula does the following:

  1. If the invoice is paid on time or before its due time, it returns “Invoice on time :thumbs_up: ”
  2. If the invoice is not yet paid AND the due date has passed, it returns “ :bangbang: ”&{Days Late}
  3. If the invoice is paid but the paid date is after the due date, it returns “ :alarm_clock: ”&{Days Late}

I believe this is what you were looking for?

BR,
Mo


Hello Again!

Nearly there!

  1. Unpaid late invoices are marked with „on time“, not „x days late“ (in the field „days late“ there is no entry

  2. it’d be nice to have the text say „ :alarm_clock: /!! x days late“ - can I just change:

    “:alarm_clock:”&{Days Late}

To

‚„:alarm_clock: &{Days Late} days late“‘

?

If it helps, in my days late field I have:

IF({Pay cycle in days} >14, DATETIME_DIFF({Date paid},{Due date},"days"))

Can you tell I‘m new at this?

Mohamed_Swella1
Forum|alt.badge.img+17

Hello Again!

Nearly there!

  1. Unpaid late invoices are marked with „on time“, not „x days late“ (in the field „days late“ there is no entry

  2. it’d be nice to have the text say „ :alarm_clock: /!! x days late“ - can I just change:

    “:alarm_clock:”&{Days Late}

To

‚„:alarm_clock: &{Days Late} days late“‘

?

If it helps, in my days late field I have:

IF({Pay cycle in days} >14, DATETIME_DIFF({Date paid},{Due date},"days"))

Can you tell I‘m new at this?


Hi Edd,

  1. The Days late has to be a formula , check the first formula.

  2. You can add &" Days Late" to both strings so it would read what you want.

We were all new to this one day, one step at a time :slightly_smiling_face: feel free to ask as many questions as you need

E
  • Edd_Turner
  • Author
  • Known Participant
  • March 20, 2020

Hi Edd,

  1. The Days late has to be a formula , check the first formula.

  2. You can add &" Days Late" to both strings so it would read what you want.

We were all new to this one day, one step at a time :slightly_smiling_face: feel free to ask as many questions as you need


Thank you for the encouraging words and the big help!

Now I have a beautifully working table, and some day I hope to be able to pluck apart the new formulas and understand them myself (and help another beginner).

All the best from Germany!

Edd

Mohamed_Swella1
Mohamed_Swella1
Forum|alt.badge.img+17

Thank you for the encouraging words and the big help!

Now I have a beautifully working table, and some day I hope to be able to pluck apart the new formulas and understand them myself (and help another beginner).

All the best from Germany!

Edd


You’re welcome Edd :slightly_smiling_face: stay safe !

Please mark your question as answered

E
  • Edd_Turner
  • Author
  • Known Participant
  • March 20, 2020

You’re welcome Edd :slightly_smiling_face: stay safe !

Please mark your question as answered


I don’t see how to mark my question as answered…

Terms & ConditionsAccessibility statement