If Statement with dates

April 21, 2019 at 06:44:30
Specs: Windows 7
I'm trying to write an If statement that if it falls between 10/1/18 thru 9/30/19, then enter "2019" but if it falls between 10/1/17 thru 9/30/18, then enter "2018".
Thank you in advance to trying to help.

See More: If Statement with dates

Report •

#1
April 21, 2019 at 07:46:16
Try this and see how it works for you:

=IF(AND(A1>=DATE(2018,10,1),A1<=DATE(2019,9,30)),2019,IF(AND(A1>=DATE(2017,10,1),A1<=DATE(2018,9,30)),2018,))

MIKE

http://www.skeptic.com/


Report •

#2
April 21, 2019 at 08:22:18
Mike
Thank you for responding so quickly! It actually worked for only a few rows. Not sure what the problem is. I copied the formula off what you send and adjusted for my columns/rows. I am working with a very large spreadsheet (234,000 rows).

Report •

#3
April 21, 2019 at 08:25:25
I think my issue has to do with the cells that my dates are in because when I edit the dates and put in the same date - the formulas work!!

Report •

Related Solutions

#4
April 21, 2019 at 08:31:10
If those are the only 2 date ranges that your referenced date will fall between, this should work:

=IF(A1=MEDIAN(A1,DATE(2017,10,1),DATE(2018,9,30)),2018,2019)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
April 21, 2019 at 08:43:29
That latest formula helped however it is came up with the wrong date "2019" when it should be "2018" for many of the dates.

Report •

#6
April 21, 2019 at 12:49:58
Please supply a few of the dates for which it produced the incorrect results.

I'm curious to see why it didn't work.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
April 21, 2019 at 13:17:43
1/4/2018
1/4/2018
1/4/2018

The first date doesn't work but if I edit the cell, delete the last digit "8" then put the "8" back in, the formula works. When I edit the date, the date gets entered in a "centered" fashion verses "Left" centered fashion as the first date above.


Report •

#8
April 21, 2019 at 13:40:26
The issue sounds like the "dates" are actually being seen as Text, not as Dates - until you edit them.

Does Mike's formula work for the entries that my suggestion does not?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
April 21, 2019 at 15:08:34
Try this and see how it works, it's first checks to make sure A1 is not TEXT
will return the word ERROR if A1 is not a number:

=IF(NOT(ISNUMBER(A1)),"ERROR",IF(AND(A1>=DATE(2018,10,1),A1<=DATE(2019,9,30)),2019,IF(AND(A1>=DATE(2017,10,1),A1<=DATE(2018,9,30)),2018,)))

MIKE

http://www.skeptic.com/


Report •

#10
April 21, 2019 at 15:34:04
Mike
Thank you for all the time put on this (thank you to "DerbyDad" also for your suggestions).

The formula that you just sent works! It is coming up "ERROR" for dates that are TEXT and comes up with the correct "IF" statement if it's a number.

Question is, is there a way to formulate the whole column to a number verses editing each cell? As I mentioned before, this spreadsheet is over 230,000 rows.


Report •

#11
April 21, 2019 at 15:47:31
I wanted to add that if I hit "F2" (edit the cell) and enter - the cell converts and the formula works. It would be easier if I could just edit the entire column so that I don't have to go through all the rows to do them individually.

Report •

#12
April 21, 2019 at 18:15:31
Try this first:

Select the entire column and Format it as Date. That might work.

If not, try this:

1 - Select any empty cell
2 - Ctrl-c to copy
3 - Select the offensive column
4 - Right-click...Paste Special...Add
5 - If need be (if the cells display a 5 digit number), select the entire column and Format it as Date.

There are 2 related reasons that this might work:

1 - Excel stores dates (and times) as numbers. Day 0 is 1/0/1900, Day 1 is 1/1/1900, Day 43576 is 4/21/2019. Therefore, if you add 1 to 4/21/2019, you'll get 4/22/2019. (Times are stored as the decimal portion of a 24 hour day. 43576.5 is 12PM on 4/21/2019)

1a - Since Excel likes to work with numbers, if it can perform a mathematical operation on a number that is formatted as Text, it will coerce text into a number and perform the operation.

Therefore, if you Add zero (the empty cell) to a Text date, it will attempt to Add zero to the underlying number that the date would be stored as. That's why it just may convert all of your Text "dates" to its internal 5 digit number, which you may then be able to format (display) as a date.

Let us know if that works for you. If it does convert them all to dates, then Mike's original formula, as well as my MEDIAN formula, should work.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#13
April 21, 2019 at 19:17:34
This formula is rather long,
but it doesn't care if the cell is formatted as a DATE or
if the cell is a TEXT cell, with a text string that LOOKS like a date:

=IF(ISNUMBER(A1),IF(AND(A1>=DATE(2018,10,1),A1<=DATE(2019,9,30)),2019,IF(AND(A1>=DATE(2017,10,1),A1<=DATE(2018,9,30)),2018,)),IF(AND(DATEVALUE(A1)>=DATE(2018,10,1),DATEVALUE(A1)<=DATE(2019,9,30)),2019,IF(AND(DATEVALUE(A1)>=DATE(2017,10,1),DATEVALUE(A1)<=DATE(2019,9,30)),2018,)))

The TEXT cell must have a valid Date format,
IE M/D/YY or MM/DD/YYYY, etc.

I would suggest you copy & paste from here.

If you are going to use the TEXT dates for further processing,
I would advise converting them as suggested by DerbyDad03

See how it works for you.

MIKE

http://www.skeptic.com/


Report •

#14
April 22, 2019 at 04:18:19
Mike
The last formula works!! Yes it is long, but it works!

DerbyDad03- your suggestion also works!!

THANK YOU both for taking the time "so quickly" to answer my question and solve my problem. I can't thank you enough.

Tim


Report •

Ask Question