Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: WORKDAY_DIFF gives negative value, only in one record

1436 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Ruben_Garcia
4 - Data Explorer
4 - Data Explorer

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

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

5 Replies 5

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.

Hi!

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

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.

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

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.