Wrong Formula Calculation

Nitzan

Hey guys,

I have two sheets: one is "Holidays", and another one is "Teams Management".

at "Teams Management" I have these columns (and also the country of the employee):

At "Holidays" I have these columns (and also the country):

I'm trying to collect for each employee the number of vacation days according to his country and according to the quarter dates. I used this formula on "Holidays" colmun at "Teams Management" sheet:

=IF(NOT(ISBLANK([Employee Name]@row)), SUM(COLLECT({Holidays Range 1}, {Holidays Range 2}, >=[Q Start Date]@row, {Holidays Range 3}, <=[Q Finish Date]@row, {Holidays Range 4}, Country@row, {Holidays Range 5}, Q@row)))

Holidays range 1= Vacation Days from "Holidays" sheet.

Holidays range 2= Start Date from "Holidays" sheet.

Holidays range 3= Finsih Date from "Holidays" sheet.

the result is a very strange number (supposed to be 7 and I get 8.5). The weirdest thing is that I don't have 0.5 days at all on "Vacation Days" column..

Any ideas where is this calculation coming from?

Thank you very much!

Paul Newcome

How is the Vacation Days column currently being calculated. It is possible there are decimals stored on the back-end that are not visible.

Nitzan

Hey Paul,

Vacation days calculation is just: (Finish Date- Start Date+1) - Weekend Days

No demicals stored in the back-end

Paul Newcome

To confirm... What happens if you show the decimals in the Vacation Days column? Is it all zeros?

Nitzan

You are right! I see now that the result is a demical number.

But why do I get these result? For example when I tested just Finish Date - Start Date I get these numbers:

Its supposed to be 1, 1,0,0,1

Paul Newcome

Do you have dependencies turned on? It looks like it is calculating based on a 9 hour "work day".

9 hours out of 24 (one day duration) is 0.375 or 37.5% of a day. Try wrapping the formula that is calculating the duration in a ROUNDDOWN or INT function.