Excel 2003 IF Function using Dates & Text [Solved/Closed]

Jen - Aug 28, 2009 at 05:40 AM - Latest reply:  depu
- Jul 9, 2014 at 02:18 AM
Hello,
I'm trying to write a function for a cell which refers to a date in another cell - which if it is between 01/01/1994 and 31/12/1995 the cell will read U16, but if the date is between 01/01/1996 and 31/12/1997 it will read U14 and if the date is later than 01/01/1998, it will read U12.

Thanks for any help.
See more 

7 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Aug 28, 2009 at 06:44 AM
19
Thank you
your date entry is in A1

try this formula

=IF(AND(A1>="1/1/94"+0,A1<="12/31/95"+0),"U16",IF(AND(A1>="1/1/96"+0,A1<="12/31/97"+0),"U14",IF(A1>=1/1/98,"U12","")))

study this carefully so that you an create such a formula in future

remember in excel the dates are entered as mm/dd/yy
that is 5/10/94 is 10th May 1994

Thank you, venkat1926 19

Something to say? Add comment

CCM has helped 1757 users this month

I want to do something similar, but what should I do instead if I wanted to have a cell colored with text written within it when the formula searches through the date ranges? If the answer is use macros, then do you have suggestions on how to make the macro....I am not an expert on how to apply macros. Thank you so much!
hello , why use +0 in this formula
2
Thank you
Fantastic! that worked perfectly. I was just about going nuts trying to work it out - and see that I was nearly there, but even being just a little wrong makes a world of difference.

Thanks for your wonderful expertise!

Regards
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Apr 11, 2010 at 09:59 PM
1
Thank you
uni111
mistake in my replly

uni111
regarding your question can you post a small extract of your data with coloring
and give examples what you want. Let us try.
greetings.
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Apr 11, 2010 at 09:58 PM
0
Thank you
http://ccm.net/...
GKaplowitz 1 Posts Friday November 22, 2013Registration date November 22, 2013 Last seen - Nov 22, 2013 at 06:26 PM
0
Thank you
This formula works!!! However, I need to add more to the formula with this concept: Let's say there are 2 columns, one column contains start date and 2nd columns contains end date, answers will go to 2 columns if for example: start date column has "7/20/2013", then end date has "8/4"/2013", the July column should have 1 as value and August should have 1 value for having value on end date for august. If both start date and end date falls within same month then the value in zugust should be "0". I need help with the formula. Thanks.