# Wrong Formula Calculation

Options
✭✭

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭
edited 03/28/23
Options

Hey Paul,

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

No demicals stored in the back-end

• ✭✭✭✭✭✭
Options

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

• ✭✭
edited 03/28/23
Options

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

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!