workdaydiff misscalculation

[Javi_BG]

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.

Thanks in advance! Javier

[RdMedia_srl]

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

[Javi_BG]

Hi Rodolfo, thanks for your reply,

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!

[RdMedia_srl]

Are you using this formula in your formula field?

If I use this formula with my Date field I get this

[RdMedia_srl · Accepted solution]

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)

[Javi_BG]

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!

[Javi_BG]

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

[RdMedia_srl · Accepted solution]

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)

[Javi_BG]

Thanks! It worked!!