Skip to main content

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
 
 

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, 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!

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

 

 

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)

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!

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

 

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)

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


Terms & Conditions