Excel 2003 IF Function using Dates & Text

Solved/Closed
Jen - Aug 28, 2009 at 05:40 AM
 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.

5 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 28, 2009 at 06:44 AM
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
19
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!
0
hello , why use +0 in this formula
0
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
2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 11, 2010 at 09:59 PM
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.
1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 11, 2010 at 09:58 PM
0

Didn't find the answer you are looking for?

Ask a question
GKaplowitz Posts 1 Registration date Friday November 22, 2013 Status Member Last seen November 22, 2013
Nov 22, 2013 at 06:26 PM
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.
0