Wrong Formula Calculation

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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!