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:
- If the invoice is paid on time or before its due time, it returns “Invoice on time :thumbs_up: ”
- If the invoice is not yet paid AND the due date has passed, it returns “
”&{Days Late}
- If the invoice is paid but the paid date is after the due date, it returns “
”&{Days Late}
I believe this is what you were looking for?
BR,
Mo
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
If your kindly offered code works
Stay healthy!
Thanks
Edd
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:
- If the invoice is paid on time or before its due time, it returns “Invoice on time :thumbs_up: ”
- If the invoice is not yet paid AND the due date has passed, it returns “
”&{Days Late}
- If the invoice is paid but the paid date is after the due date, it returns “
”&{Days Late}
I believe this is what you were looking for?
BR,
Mo
Hello Again!
Nearly there!
Unpaid late invoices are marked with „on time“, not „x days late“ (in the field „days late“ there is no entry
it’d be nice to have the text say „
/!! x days late“ - can I just change:
“
”&{Days Late}
To
‚„
&{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?
Hello Again!
Nearly there!
Unpaid late invoices are marked with „on time“, not „x days late“ (in the field „days late“ there is no entry
it’d be nice to have the text say „
/!! x days late“ - can I just change:
“
”&{Days Late}
To
‚„
&{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,
The Days late has to be a formula , check the first formula.
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
feel free to ask as many questions as you need
Hi Edd,
The Days late has to be a formula , check the first formula.
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
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
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
stay safe !
Please mark your question as answered
You’re welcome Edd
stay safe !
Please mark your question as answered
I don’t see how to mark my question as answered…