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.

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

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).

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!!

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

That latest formula helped however it is came up with the wrong date "2019" when it should be "2018" for many of the dates.

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

1/4/2018

1/4/2018

1/4/2018The 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.

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

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

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.

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.

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

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 DerbyDad03See how it works for you.

MIKE

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

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History