This should be an easy formula, need help

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

SteveH said:
I am working on a spreadsheet with dates and I am needing the cell to add
Click to expand...
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
Click to expand...
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
Click to expand...
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.
 
Back
Top