Formula for looking at 1 cell with a date and then comparing it to four date ranges

LearningMom · #1 10-27-2017, 07:25 AM

We have one column with a last date gave. We want to write a formula that looks at that cell and outputs a "0" if it is before 06/30/2014 outputs a "1" if the date in the cell is between 7/1/2014 and 6/30/2015 and then a "2" if it is between 7/1/15 and 6/30/16 and a "3" between 7/1/16 and 6/30/18

Thank you

LearningMom · #2 10-27-2017, 11:47 AM

I thought I would show you the formula a have been tweaking with no success. I did get a 1 once but when I added the third formula it stopped with a error message.

=IF(AND(M4955<=DATE(2014,30,6)),0,IF(AND(M4955<=DA TE(2014,1,7),M4955<=DATE(2015,30,6)),1))

NBVC · #3 10-27-2017, 12:09 PM

Maybe?

Code:

=IF(M4955<=DATE(2014,6,30),0,IF(AND(M4955>=DATE(2014,7,1),M4955<=DATE(2015,6,30)),1,IF(AND(M4955>=DATE(2015,7,1),M4955<=DATE(2016,6,30)),2,IF(AND(M4955>=DATE(2016,7,1),M4955<=DATE(2018,6,30)),3,""))))

LearningMom · #4 10-27-2017, 12:44 PM

Thank you, clearly the first AND I didn't think of removing. I am blaming trying way to many ideas and not returning to the original for the multitude of errors.

Thank you again.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Index match two date ranges	toddkad	Excel	9	11-03-2016 03:10 AM
Counting How many Times a date appears between two ranges of dates	Mars	Excel	1	10-21-2016 02:21 PM
calculate date if date entered in cell, do nothing if blank	ConfuddledOne	Excel	3	11-07-2014 09:37 AM
Inconsistent Duration column for the same date ranges	ravl13	Project	3	07-28-2014 05:16 PM
Make table cell active add date count date	MelHerndon	Word VBA	3	01-21-2013 04:23 PM