Skip to main content
Solved

workdaydiff misscalculation

J
Forum|alt.badge.img+2
  • Javi_BG
  • Participating Frequently
  • 7 replies

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
 
 

Best answer by RdMedia_srl

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)
View original
Did this topic help you find an answer to your question?

8 replies

R
Forum|alt.badge.img+10
  • RdMedia_srl
  • Inspiring
  • 36 replies
  • April 14, 2023

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
J
Forum|alt.badge.img+2
  • Javi_BG
  • Author
  • Participating Frequently
  • 7 replies
  • April 14, 2023
RdMedia_srl wrote:

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

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!

R
Forum|alt.badge.img+10
  • RdMedia_srl
  • Inspiring
  • 36 replies
  • April 14, 2023
Javi_BG wrote:

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?

 

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

 

 

Rodolfo - RdMedia
R
Forum|alt.badge.img+10
  • RdMedia_srl
  • Inspiring
  • 36 replies
  • Answer
  • April 14, 2023

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
J
Forum|alt.badge.img+2
  • Javi_BG
  • Author
  • Participating Frequently
  • 7 replies
  • April 17, 2023

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!

J
Forum|alt.badge.img+2
  • Javi_BG
  • Author
  • Participating Frequently
  • 7 replies
  • April 20, 2023

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

 

R
Forum|alt.badge.img+10
  • RdMedia_srl
  • Inspiring
  • 36 replies
  • April 20, 2023

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
J
Forum|alt.badge.img+2
  • Javi_BG
  • Author
  • Participating Frequently
  • 7 replies
  • April 26, 2023
RdMedia_srl wrote:

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)

Thanks! It worked!!

Reply


Most helpful members this week

Terms & Conditions