# workdaydiff misscalculation

Topic Labels: Formulas
Solved
374 8
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

Hi there,

I'm experiencing a strange situation with a simple formula. Here's what i'm using:

WORKDAY_DIFF({Date-test-1}, {Date-test-2})-1

I set date-test-1 to Today's date (Friday). If I set "Date-test-2" to next Monday, the result is correct: 1
(Note that my dates are in european format) If I set it to next Friday, the result is correct: 5 It seems that it does it perfectly whenever Date test is a future date.

I also want to calculate the working days between today and a past date, and here's where i'm getting strange results:
Setting Date-test-2 to Yesterday, Thursday, the result is: -3, should be -1 Setting it to last Friday, the result is: -7, should be -5

Any explanation on why this is is happening? I could do an easy trick, and add +2 whenever the date is past, but I can't understand why this is happening, so i don't know if this fix would stay consistent.

2 Solutions

Accepted Solutions  6 - Interface Innovator

Try like this ù

You have a hidden "calculation field with this formula

DATETIME_DIFF({Date-test2},{Date-test1}, 'days') And then this formula in the Working days field

IF( Calculation > 0 , WORKDAY_DIFF( {Date-test1},{Date-test2})-1,(WORKDAY_DIFF({Date-test2}, {Date-test1})-1)*-1) Rodolfo - RdMedia  6 - Interface Innovator

mmm you should follow the instructions above

You have a hidden "calculation field with this formula

DATETIME_DIFF({Date-test2},{Date-test1}, 'days')

And then this formula in the Working days field

IF( Calculation > 0 , WORKDAY_DIFF( {Date-test1},{Date-test2})-1,(WORKDAY_DIFF({Date-test2}, {Date-test1})-1)*-1)

Rodolfo - RdMedia
8 Replies 8  6 - Interface Innovator

Hi, you could try using this formula

IF( DATETIME_DIFF(Date, TODAY(), 'days') >= 0, WORKDAY_DIFF(TODAY(), Date)-1, (WORKDAY_DIFF(Date, TODAY())-1)*-1)

for days before today you need to reverse the two factors in the formula

so if DATE its equal or grater than TODAY you use WORKDAY_DIFF(TODAY(), Date)-1,

otherwise, if it's before TODAY you use this and multiply the result by -1 to get the negative number

(WORKDAY_DIFF(Date, TODAY())-1)*-1)

let me know

Rodolfo - RdMedia  5 - Automation Enthusiast

Unfortunately, that didn't do the job. The error i'm getting is not that i get a positive number that should be negative. The thing is, calculating workdays between today (Friday) and yesterday, i'm getting 3, instead of 1.

Thanks!  6 - Interface Innovator

Are you using this formula in your formula field? If I use this formula with my Date field I get this Rodolfo - RdMedia  6 - Interface Innovator

Try like this ù

You have a hidden "calculation field with this formula

DATETIME_DIFF({Date-test2},{Date-test1}, 'days') And then this formula in the Working days field

IF( Calculation > 0 , WORKDAY_DIFF( {Date-test1},{Date-test2})-1,(WORKDAY_DIFF({Date-test2}, {Date-test1})-1)*-1) Rodolfo - RdMedia  5 - Automation Enthusiast

Hi there!

It's working! Thanks! I don't know how i set it up before, but now it seems to be working perfectly!

Thanks a lot!  5 - Automation Enthusiast

Hi there,

This formula doesn't seem to be working when trying to get the number of working days from today to yesterday. It works apparently well in other cases.

Being today 20/04/2023, I get -3 instead of -1: Here's the formula, just in case:

IF( DATETIME_DIFF({Fecha Experiencia}, TODAY(), 'days') >= 0, WORKDAY_DIFF(TODAY(), {Fecha Experiencia})-1, (WORKDAY_DIFF({Fecha Experiencia}, TODAY())-1)*-1)

Any clue on why is this happening? Thanks in advance,

Javier  6 - Interface Innovator

mmm you should follow the instructions above

You have a hidden "calculation field with this formula

DATETIME_DIFF({Date-test2},{Date-test1}, 'days')

And then this formula in the Working days field

IF( Calculation > 0 , WORKDAY_DIFF( {Date-test1},{Date-test2})-1,(WORKDAY_DIFF({Date-test2}, {Date-test1})-1)*-1)

Rodolfo - RdMedia  5 - Automation Enthusiast

Thanks! It worked!! 