Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 09, 2021 05:48 AM
Hi!
I have a formula to calculate workdays difference between opened ticket and closed status, or today if not closed. Works great in the rest of cases, but in a specific record, a negative value is given, while a record with similar values returns the correct amount.
Below is a screenshot, the translated terms are:
Open days for this ticket, Created ticket date, :date: Solved ticket date
The formula for the “open days for this ticket” is like this:
IF(
(IS_BEFORE({ :date: Solved ticket date},TODAY())),
(WORKDAY_DIFF({ :date: Solved ticket date},{Created ticket date})),
(WORKDAY_DIFF({Created ticket date},TODAY()))
)
and the formula for :date: Solved ticket date is:
IF( Status = “Finalizado” , DATETIME_FORMAT({Estado cambiado} , ‘DD/MM/YYYY’),BLANK())
Feb 09, 2021 07:28 AM
You are getting a negative number because for that record, the {Solved ticket date} (26 January 2021) is before the {Created ticket date} (2 February 2021).
For the other records the {Solved ticket date} ( 26 January 20201) is after the {Created ticket date} (2 January 20201). When there is no {Solved ticket date} you are comparing with TODAY(), and you flipped the order of the inputs to WORKDAY_DIFF
.
Feb 09, 2021 08:09 AM
Hi!
Both dates are DD/MM/YYYY, because is in european format, unless WORKDAY_DIFF uses US format anyways.
Feb 09, 2021 09:20 AM
Sorry for the typo when I was looking at your original post. I got the before/after mixed up because you have the inputs to WORKDAY_DIFF
in different orders in the two functions.
I went back and edited my original post to be more clear.
Using the European style formatting does not affect Airtable’s calculations.
Feb 10, 2021 11:41 AM
Thank you, what a silly mistake…
I have updated the formula to:
IF(
(IS_BEFORE({ :date: Solved ticket date},TODAY())),
(WORKDAY_DIFF({Created ticket date},{ :date: Solved ticket date})),
(WORKDAY_DIFF({Created ticket date},TODAY()))
)
but I am getting a similar error in another record this time
Feb 10, 2021 09:11 PM
It’s the exact same issue again. The function takes two dates. Depending on which of the two dates is earlier, you will get either a positive or a negative number. In your data, sometimes the created date is first, sometimes the solved date is first.