Cycle Time if in a Specific Year

[PAL]

I am trying to calculate the time between 2 dates, if the latter date
occurred in 2007. Here is where I was going, but it doesn't seem to work.

=IF(H2=YEAR(L2),(H2-E2),"") where L2 has the value 2007.

Also, how would it change if I wanted H2 beween 2 years, ie. 2004-2006.

Thanks.

 

[David Biddulph]

If you are struggling to see what is happening in a formula, you could
always break it down into manageable chunks until you can see what's going
on.

If you look at =YEAR(2007) it will return the value 1905, because when you
treat the number 2007 as a date (by using it as the input to the YEAR
function) then it is interpreted as 29th June 1905 (2007 days after the
beginning of 1900). If you'd forgotten what the YEAR function does, you can
look it up in Excel help.
If you are trying to test whether a date in H2 is in 2007, it looks as if
you want to change your formula to
=IF(YEAR(H2)=L2,H2-E2,"")
If you want to test whether H2 falls within your 3 year range (2004 to
2006), then you could use
=IF(AND(YEAR(H2)>=2004,YEAR(H2)<=2006),H2-E2,"")
[or you could put 2004 and 2006 in cells and use the relevant cell
references in the formula, as you did for 2007 in L2]

 

[T. Valko]

Do H2 and E2 contain dates?

If so, try it like this:

=IF(YEAR(H2)=L2,H2-E2,"")