Help with formula?

[Guest]

Please help formatting this formula:
=If B16(hire date) < 90 days then 0,
If B16 > 90 days then B16(hire date) + End of that Year(Dec 31st of same
year) * 0.0274( rounded up, if results are 4.1 then 4.5) Thanks in Advance!!!

 

[Guest]

do you mean:

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)+0.5)


HTH

 

[Guest]

..... rounding up ...

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)/0.5)*0.5+0.5

 

[Guest]

Yes, this works great but I need it to only do this for the first part of the
first year. After the first year I have a vlookup. example:
=IF(DATEDIF(B16,TODAY(),"Y")>11,20,INT(VLOOKUP(DATEDIF(B16,TODAY(),"Y"),K:L,2,0))&" Days "&8*MOD(VLOOKUP(DATEDIF(B16,TODAY(),"Y"),K:L,2,0),1)& " hours")

 

[Sandy Mann]

.... rounding up
....

=IF(TODAY()-B16<90,0,INT((DATE(YEAR(B16),12,31)-B16)*0.0274)/0.5)*0.5+0.5

Because the *0.5+0.5 is outside of the function it returns 0.5 for me
instead of 0. Wouldn't it be better to use the ROUBD function instead of
INT()?

=IF(TODAY()-B16<90,0,ROUND(((DATE(YEAR(B16),12,31)-B16)*0.0274)/0.5,0)*0.5)

--
Regards,

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk