WORKDAY_DIFF gives negative value, only in one record

Ask the community Formulas
#1

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

image
image603×714 11.7 KB

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())

#2

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.

#3

Hi!

Both dates are DD/MM/YYYY, because is in european format, unless WORKDAY_DIFF uses US format anyways.
image
image

#4

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.

#5

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
image

#6

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.

#7

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.