This should be an easy formula, need help

[Guest]

I am working on a spreadsheet with dates and I am needing the cell to add 364 or 365 days depending on whether or not we are in a leap year situation. I have determined that if the user inputs a beginning date between 37681 (3/1/2003) and 38046 (2/29/2004) then the end date needs to add 365 days for it to be one full year. If the begin date is any other value, adding 364 will be one full year

I thought a formula such as =if(37681<=A1<=38046,A1+365,A1+364) would have worked, but it does not. So how do we determine if a number falls between 2 values

I know I could embed multiple if statements to accomplish this, but I already have to determine if the employee terminated during the year to use the termination date as the output, or if no value is entered into the begin date (A1) the cell should be blank. I am trying to chop down the size of my formula

Thanks.

 

[Niek Otten]

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

I am working on a spreadsheet with dates and I am needing the cell to add

364 or 365 days depending on whether or not we are in a leap year situation.
I have determined that if the user inputs a beginning date between 37681
(3/1/2003) and 38046 (2/29/2004) then the end date needs to add 365 days for
it to be one full year. If the begin date is any other value, adding 364
will be one full year.

I thought a formula such as =if(37681<=A1<=38046,A1+365,A1+364) would have

worked, but it does not. So how do we determine if a number falls between 2
values?

I know I could embed multiple if statements to accomplish this, but I

already have to determine if the employee terminated during the year to use
the termination date as the output, or if no value is entered into the begin
date (A1) the cell should be blank. I am trying to chop down the size of my
formula.

 

[Guest]

Works perfectly thanks Nick

 

[Frank Kabel]

Hi
try the following if A1 stores your date
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))