Date sumif 1

[icsupt]

How would I do this?

If A2 is >= 1/1/2003 and <12/31/2003, put "2003" in T2
If A2 is >= 1/1/2004 and <=12/31/2004, put "2004" in T2
If A2 is >= 1/1/2005 and <=5/31/2005, put "1H2005" in T2
If A2 is >= 6/1/2005 and <=12/31/2005, put "2H2005" in T2
If A2 is >12/31/2005, put "2006 or over"

Thanks in advance.

 

[macropod]

Hi,

How about:
=IF(YEAR(A2)<2005,YEAR(A2),IF(YEAR(A2)>2005,"2006 or over",IF(MONTH(A2)<7,"1H2005","2H2005")))

Cheers

 

[AnotherHiggins]

=if(A2>datevalue("12/31/2005"),"2006 or Over",if(a2>=datevalue("06/04/2005"),"2H2005",if(a2>=datevalue("01/01/2005"),"1H2005",
if(a2>=datevalue("01/01/2004"),"2004",if(a2>=datevalue("01/01/2003"),"2003","2002 or Before")))))

The above is the first thing that came to mind when I read the question. Then I went out for a bit and apparently forgot to press 'submit' before leaving. I like macropod's suggestion, but just for the sake of skinning the poor cat another way, I'll post this anyway.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

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

 

[xlbo]

This cat is gonna get hammered 'cos I would suggest the easiest way to cope with these kinda thiongs is a lookup table in conjunction with the VLOOKUP() formula with the 4th Argument set to FALSE - much easier to update when you need to add more time periods.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Amen to that

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

D'oh - too early - for this, the 4th argument would be set to TRUE (so that it can look up non exact matches), not false as I put befoe (shame on you Ken for not spotting that ;-) )

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL - never even looked at the syntax you were suggesting Geoff, just saw the VLOOKUP suggestion and agreed that would be the best way to play it. But you're right - my bad

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

heh heh - you been a bit quiet for a while Ken - been on hols ?

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Unfortunately not - Have been working a huge bid (circa £1B, and extremely complex in it's makeup), and have been working silly hours, weekends and all, to try and pull it off. Is almost done now and am just working on a supplementary bid to follow it up, but have also just been offered a new job on a different side of the business which I'm transitioning to this week, so life is mega hectic at the moment to say the least. On top of all that I badly screwed my back up somehow a couple of weeks ago, and it hurts quite a bit.

All of that has pretty much curtailed a lot of what I was doing both here and in the MS newsgroups, but hopefully I can get back to normal soon.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

ooooh - sounds nasty Ken (work and back). Did my back in a while ago - turned out that a snowboarding holiday was the best possible cure !!! (well - havn't had a twinge since then anyway ;-) )

Good luck with the bid and it'll be good to see you back here again.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Cheers Geoff. I've never had backache in my life, and now have the utmost sympathy for anyone that does

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Yup - that was my experience as well - the sheer frustration of not being able to put your own socks on !!!

Anyways - better not continue this social chitchat - we gotta proper forum for that ;-)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question