Menu
Ask a question Report

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

- - Latest reply:  depu - Jul 9, 2014 at 02:18 AM
Excel Ask a question Report
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 

5 replies

Best answer
Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
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

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2559 users this month

Report
-
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!
Report
-
hello , why use +0 in this formula
Report Respond to Jen
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
Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
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.
Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
0
Thank you
http://ccm.net/...
Report Respond to GKaplowitz
Posts
1
Registration date
Friday November 22, 2013
Last seen
November 22, 2013
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.
Ask a question