Excel - IF Formula with dates

[Livia8]

Hi there,

I have two ranges of dates in columns A and B.
I'm trying to find the correct formula to use to have the system tell me that if A1>31/12/04 and B1<01/01/06, then the result is "10"; if A1>31/12/03 and B1<01/01/05, then the result is "9" and so on.

How do you link a date comparison with an IF Formula?
If not an IF Formula, what's the best formula to use?

I've checked HELP in excel but I couldn't find anything relevant.

Many thanks.

 

[AnotherHiggins]

So, basically, you want to add 5 to the last digit of the year, right? What do you want to do for years before 2000?

For the two example-periods you gave, you could use the following logic:
Let's look at the date 1 July, 2005
=YEAR(A1)
will return 2005

=YEAR(A1)-2000
will return 5

=YEAR(A1)-2000+5
will return 10

So shorten that up to
[COLOR=blue white]=YEAR(A1)-1995[/color]
to get 10.

Make sure to format the cell as General or Number to properly display your answer.

As I said, you will need to provide information about what to do with pre-2000 years for a full formula that takes that logic into account.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Livia8]

John,

Thanks for that but it's not exactly what I was aiming at:
I'm trying to compare the two dates, to see if those dates are within certain parameters (i.e. if A1>31DEC04 and if B1< 01JAN06, or A1>31DEC03 and B1<31JAN06, etc).

It's basically to see the life-span of a product; so, in the first instance the product has 10 years left, in the second nine, and so on.

Thanks,

Livia

 

[adenough]

Hello Livia,

Excuse my ignorance on this but you seem to be working on full calendar years. If that's the case this seems to do the job?

=10-((YEAR(TODAY()))-(YEAR(A1)))

Sean

 

[Livia8]

Thanks Sean,

This works perfectly if I use only one date, but I need to compare the two dates in cells A and B before I apply the formula. In other words, I have to check that the date in A1 is greater than 31/12/04 and that the date in B1 is smaller than 01/01/05. Ideally I'd need to check that A1 is >31/12/04 but <01/01/05, and that B2 is >31/12/05 but < 01/01/06 (and so on).

Any other idea?

Thanks.

L.

 

[bluedragon2]

=IF(AND(A1>date1,B1<date2),10,0)


[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Sorry, hit submit by mistake.

That was the syntax for an AND IF statement, but since you have multiple conditions, you either need a lookup table for your result, or a formula to get the number you want. If you describe your paramenters a bit more, maybe we will be able to be of more assistance.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SilentAiche]

Hi Livia,

I'm afraid I'm not following this one very well.

Could you post some actual data (at least five rows or so)?

Also, is it always true that B1>A1, B2>A2, etc.? Can B2 and A2 be from different years? Is B2-A2 always 1 year? Do any of the years predate 01/01/2000?

Ideally I'd need to check that A1 is >31/12/[red]04[/red] but <01/01/05, and that B2 is >31/12/[red]05[/red] but < 01/01/06 (and so on).

(my emphasis added)

You almost certainly meant for your examples to read 03 and 04, respectively (otherwise, no time elapses!)

Thanks,
Tim


[blue]______________________________________________________________
I love logging onto Tek-Tips. It's always so exciting to see what the hell I
said yesterday. [/blue]

 

[Livia8]

Hi Silent Aiche,

Sorry for not getting back to you sooner, I only got around checking this site again today.

Q. Is it always true that B1>A1, B2>A2, etc.? Is B2-A2 always 1 year?
A. Basically, B is always greater than A, but not necessarily by 1 whole year (for instance, A1 may be 05/06/2004 and B1 may be 10/11/04.

Q. Can B2 and A2 be from different years?
A. Yes

Q: Do any of the years predate 01/01/2000
A. Yes, some of the dates may predate 2000.

Just to give you an idea, the first date is the date a product was purchased. The second date is a deadline. The "10", "9", etc which I'm trying to obtain are the "shelflife" left in the product based on the two given dates.

Now I'm wondering: would Access be a better application to use?

Thanks.