Solved If statement with dates

May 7, 2019 at 11:47:46
Specs: Windows 10
How do I write an "IF" statement for: If A2 is greater than A3 but less than A4, then yes. If not, then no. A2, A3, A4 are all dates.

message edited by JudiA


See More: If statement with dates

Report •

#1
May 7, 2019 at 11:52:24
✔ Best Answer
Try this IF-AND formula:

=IF(AND(A2>A3,A2<A4),"yes","no")

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


Report •

#2
May 7, 2019 at 12:34:20
This worked! Thank you very much!

Report •

#3
May 7, 2019 at 13:02:55
But do you know why it worked? ;-)

message edited by DerbyDad03


Report •

Related Solutions

#4
May 7, 2019 at 13:08:15
I had the same formula but it wasn't working until I copied and pasted this formula. I must have had something wrong :).

Report •

#5
May 7, 2019 at 13:11:02
Hang in ‘cos DD will explain... especially if you can post your attempt as well.

Report •

#6
May 7, 2019 at 13:11:22
So what if I wanted to created a formula that stated if A2 and B2 then (date) "1/29/19"? Is it possible?

Report •

#7
May 7, 2019 at 15:05:48
I'm not sure what you mean by "if A2 and B2"

If A2 and B2 what?

I'm also not sure what you mean by "then (date) 1/29/19"

You did a great job of explaining your requirements in your first post. This latest one? Not so much. ;-)

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


Report •

#8
May 7, 2019 at 15:45:51
Sorry about that. I've been immersed in all this.

Column C needs a formula. Columns A and B have product identification information (text). Column C needs a formula something like this "If it is P558 (Column A) and KTP (Column B) then Start Date is "6/30/19" (Column C). I have seven combinations like this over several rows that have other columns with specific information that will be updated daily but since the row order changes with each update, I need a formula to maintain the integrity of the Start Date. There doesn't seem to be a way to attach a sample (or even insert it).

I greatly appreciate your effort, even if what I'm asking doesn't exist. :)


Report •

#9
May 7, 2019 at 18:12:19
Please click on the link at the bottom of this post and read the instructions on how to post example data in this forum. Through the use of the pre tags, trial, error and multiple uses of the Preview button, you should be able to show us something like this:

          A             B             C
1        P558          KTP         6/30/2019
2        Q765          YRQ         7/3/2019
3        F543          FCD        10/5/2019


Always Include Column letters and Row numbers so that we can offer formulas based directly on the example data shown and not have to guess which Columns and Rows we are dealing with.

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


Report •

#10
May 8, 2019 at 05:37:58
This is exactly how my spreadsheet looks (with more columns of other data). I'm trying to get the date in column C to be 6/30/19 for every line in my sheet that has the same information in columns A & B. I could filter and do it manually, but the information in columns A & B changes because its linked to different cells each time I update.

Report •

#11
May 8, 2019 at 07:24:02
I don't understand what you mean by "the same information in columns A & B".

In Response #8 you said "If it is P558 (Column A) and KTP (Column B)"

P558 and KTP are not the same

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


Report •

#12
May 8, 2019 at 08:09:52
I've now concatenated columns A & B into column C. Is there a way to create a formula that will assign a specific date (to be manually entered) based on the information in column C?

Report •

#13
May 8, 2019 at 11:04:03
I'd really like to help, but you are not being clear with your requirements, Please keep in mind that we can't see your workbook from where we're sitting, nor we know what your ultimate goal is.

Is there a way to create a formula that will assign a specific date (to be manually entered)"

Why would you need a formula if you are going to manually enter a date? Those 2 things are the exact opposite of each other.

"Is there a way to create a formula that will assign a specific date (to be manually entered) based on the information in column C?"

Yes, and without more specific information, that's all I can say. Yes, a formula could do that.

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


Report •

#14
May 8, 2019 at 15:09:05
Obviously, I'm new at this and I find that you are a very patient person. I would quit after the first confusing request.

Skip the "manually" comment.

Since I've concatenated columns A & B, my table now looks like this. Is it possible to have a formula in column B that will give a specific date based on data in column A? In this case, a formula that will give me the date of 6/30/19 for data in A1, the date of 7/3/19 for data in A2, and the date of 10/5/19 for data in A3.

I hope this is clear enough :(. Thank you, once again, for your patience.

A B (start date)
1 P558 KTP 6/30/19 (formula)
2 Q765 YRQ 7/3/19 (formula)
3 F543 FCD 10/5/19 (formula)


Report •

#15
May 8, 2019 at 15:28:23
You didn't use the pre tags as I instructed therefore I can't tell how your data is set up.

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

message edited by DerbyDad03


Report •

#16
May 8, 2019 at 17:41:26
I think I did it right this time. I need a formula in column B that will give me a specific date for data in each cell in column A. So if any cell in column A has P558 KTP entered into it, it should be assigned a date of 6/30/19 in column B. If any cell in column A has Q765 YRQ entered into it, it should be assigned a date of 7/3/19. If any cell in column A has F543 FCD entered into it, it should be assigned a date of 10/5/19. Good heavens, you're a saint - so very patient. If this happens, it will be a lifesaver! Thank you!

       A                      B 
1     P558  KTP         6/30/19 (derived from a formula)
2     Q765  YRQ        7/3/19 (derived from a formula)
3     F543  FCD        10/5/19 (derived from a formula)


Report •

#17
May 8, 2019 at 19:45:26
If you only have those few combinations to evaluate, you could use a Nested IF:

=IF(A1="P558  KTP",DATE(2019,6,30),IF(A1="Q765  YRQ",DATE(2019,7,3),DATE(2019,10,5)))

3 items:

1 - I used the DATE function to ensure that the value that the formula places in the cell is an actual Excel Date, not just a text date. Using "6/30/19" in the formula would have resulted in a text string that looks like a date but may not act like one.

2 - You have 2 spaces between the strings in your example data, so I had to use 2 spaces in the formula or they would not match.

3 - You will note that I did not test for "F543 FCD". If all you have is those 3 pieces of data, then we only need to test for 2. If it's not one of those 2, it must be the 3rd.

If you have any other strings or blank cells, then the formula needs more work.

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

message edited by DerbyDad03


Report •

#18
May 8, 2019 at 19:58:17
This is the start I was hoping for! I'm flying to NY for the day tomorrow so I will try this on Friday when I return. I think this is enough for me. I can probably play with it if it doesn't work. I'll follow up on Friday and let you know. This has been a profound learning experience for me. I'm guessing you're and IT guy? Thank you DerbyDad!

Report •

#19
May 9, 2019 at 06:09:05
Not an IT guy (anymore), just a guy that likes to play with Excel.

I will be traveling this weekend myself and don't know how much time I'll have to spend in this forum. I'll check in if time allows.

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


Report •

Ask Question