Formula not working anymore

Julie Barbeau

Hi,

I have this formula that worked in the past and now is not working, I don't know since when.

=16 - (SUMIFS({Tems en Maladie}; {Employee name}; [Employee name]@row; {Date}; YEAR(@cell) = YEAR(TODAY())))

Now the cell shows #INVALID DATA TYPE

How can this happens?

Paul Newcome

This error comes from having blanks or non-date values in the {Date} range which causes the YEAR function to error out (since it is looking for a date value to pull the year from). Try this:

=16 - (SUMIFS({Tems en Maladie}; {Employee name}; [Employee name]@row; {Date}; IFERROR(YEAR(@cell); 0) = YEAR(TODAY())))

Paul Newcome

This error comes from having blanks or non-date values in the {Date} range which causes the YEAR function to error out (since it is looking for a date value to pull the year from). Try this:

=16 - (SUMIFS({Tems en Maladie}; {Employee name}; [Employee name]@row; {Date}; IFERROR(YEAR(@cell); 0) = YEAR(TODAY())))

Julie Barbeau

@Paul Newcome works perfectly thank you!!!

Paul Newcome

Happy to help. 👍️

Julie Barbeau

@Paul Newcome Hi Paul, new problem. This formula is working in one sheet but not in the other : =INDEX(COLLECT({Numéro de période}; {Date de début - Période de paie}; @cell <= Date3; {Date de fin - Période de paie}; @cell >= Date3); 1)

Do you see something wrong?

Paul Newcome

I don't see any syntax issues. Are you getting an error or an unexpected result?