Help

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

Re: workdaydiff misscalculation

Solved
Jump to Solution
2393 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Javi_BG
5 - Automation Enthusiast
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)
Javi_BG_1-1681491840568.png

 


If I set it to next Friday, the result is correct: 5
Javi_BG_2-1681491878554.png

 

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
Javi_BG_3-1681491921318.png

 

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
 
 
2 Solutions

Accepted Solutions
RdMedia_srl
7 - App Architect
7 - App Architect

Try like this

Schermata 2023-04-14 alle 19.54.22.pngù

You have a hidden "calculation field with this formula

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

Schermata 2023-04-14 alle 19.53.05.png

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)
Schermata 2023-04-14 alle 19.53.23.png

Rodolfo - RdMedia

See Solution in Thread

RdMedia_srl
7 - App Architect
7 - App Architect

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

See Solution in Thread

8 Replies 8
RdMedia_srl
7 - App Architect
7 - App Architect

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

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!

Are you using this formula in your formula field?

Schermata 2023-04-14 alle 19.02.56.png

 

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

 

Schermata 2023-04-14 alle 19.04.01.png

 


Rodolfo - RdMedia
RdMedia_srl
7 - App Architect
7 - App Architect

Try like this

Schermata 2023-04-14 alle 19.54.22.pngù

You have a hidden "calculation field with this formula

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

Schermata 2023-04-14 alle 19.53.05.png

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)
Schermata 2023-04-14 alle 19.53.23.png

Rodolfo - RdMedia
Javi_BG
5 - Automation Enthusiast
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!

Javi_BG
5 - Automation Enthusiast
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:

Javi_BG_0-1682001456028.png

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
7 - App Architect
7 - App Architect

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

Thanks! It worked!!