Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Workday_diff miss counting

Topic Labels: Formulas
Solved
Jump to Solution
777 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello, everyone!

Could you guys lend me a hand, please?

I am using this formula to calculate the number of delayed or advanced days for a step in a project development:

IF(AND({01 Limite}, {01 Entrega}), WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’), 0)

But for some reason it is miss counting. For example: right now 01 LIMITE is 11/05/2020 and 01 ENTREGA is 11/05/2020, and the answer is ‘1’. Should be 0, right?

Thanks in advance!

1 Solution

Accepted Solutions

What are you trying to accomplish? I sometimes break up my longer formulas into different formula fields, so you can really see what the different results are for each part of your formula. Then, you could create another formula field that compares those fields to each other.

See Solution in Thread

4 Replies 4

The WORKDAY_DIFF formula includes both the start date & the end date as part of its count of workdays — not the number of workdays that have elapsed IN BETWEEN those days. In other words, the formula is INCLUSIVE of the start date & end date, not EXCLUSIVE of the start date & end date.

So a start date of 11/5/2020 and an end date of 11/5/2020 is 1, because 11/5/2020 is a workday.

Now if 11/5/2020 was NOT a workday, then it would result in 0.

If you want to exclude the start date from your count, you could just subtract 1 from the function like this:

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

Ahh, i see! Thanks!

But how can I track the days if its negative, please? My idea is to create a ‘bank of hours’ of the many steps of the project. And with the formula like this:

IF(AND({01 Limite}, {01 Entrega}), WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’) -1, 0)

The result is getting more stranger. Example: 01 limite = 11/05/2020; 01 entrega = 10/05/2020 => answer: -2. Should be -1

I tried this way:

IF(AND({01 Limite}, {01 Entrega}), IF(WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’) -1 >= 0, WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’) -1, WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’), 0)

And ist not working. Could you help me once more, please?

@ScottWorld Could you help me here, please? I really dont know what to do =((

Thanks and sorry for tagging you.

I saw an error but still doesnt work:

IF(AND({01 Limite}, {01 Entrega}), IF(WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’) -1 >= 0, WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’) -1, WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’)), 0)

What are you trying to accomplish? I sometimes break up my longer formulas into different formula fields, so you can really see what the different results are for each part of your formula. Then, you could create another formula field that compares those fields to each other.