If "value" can bedivided by 5 then....

[andrewvanmarle]

Hi All,

I have a list of national holdays and the current year in A1

This is the formula for the date:

=DATUM(A1;5;5)

And it works, but......

We wouldnt be dutch if we didnt make things complicated:

This is only a holiday every fith year.


So i'm looking for something that goes IF a1 is divisible by 5 then show the above date else NULL

What would that formula look like?

 

[FormR]

Hi, something like this maybe.

=IF(MOD(A1;5)=0;DATUM(A1;5;5);"")

 

[andrewvanmarle]

Super!!

the dutch bversion looks like this by the way (for the dutchies on the forum)

=ALS(REST(A1;5)=0;DATUM(A1;5;5);"")

 

[andrewvanmarle]

Hmmmm I have a new conundrum:

Im using this code to calculate workdays with a list of holidays (the H-range) to be counted as non work day.

=ALS(OF(A5="";B5="");"";NETWERKDAGEN.INTL(A5;B5;2;$H$3:$H$17))

When the above code comes up with a date it works fine, but when it comes up with Null, the code gives a invalid value warning.

"" isnt the same as an empty cell?

 

[FormR]

"" isnt the same as an empty cell?

Hi, no - a formula can't return "nothing" but you could check what the second formula returned and do something different when it returns "".

For example:

=ALS(A1="";"";IF(REST(A1;5)=0;DATUM(A1;5;5);""))

 

[andrewvanmarle]

Ah, well fixed it by addding a zero to the last ""

that gives a date, but so far in the past that it isnt relevant.

TY!