Using IF function with dates

Solved/Closed
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 - Nov 2, 2015 at 03:58 PM
 RayH - Nov 4, 2015 at 12:42 PM
Hello,

I want to thank everyone for their time in advance.

My problem is that I have a two cells/rows (78 & 87) in a column on this worksheet that I have created that has a date that is entered into its cell. The user that is inputting the data into the cells does sometimes leave the cells blank or sometimes someone puts "na" in it. Therefore causing my formula to be more complex to cover all possibilities of what can be in the cell.

What I need my formula to do is look at the two cells and if there is a date in the cell count 1 if blank or "na" than count it as zero and then add the two cells together for the result to be placed in cell/row 97 with their corresponding columns.

This is the formula I tried:

=IF(OR(B78="",B78="NA",B78="N/A",B78="n/a"),0,IF(OR(B87="",B87="NA",B87="N/A",B87="n/a"),0,1))

Thank you for your help.

6 replies

Blocked Profile
Nov 2, 2015 at 05:52 PM
I would suggest as a short cut for now, Separating the two cells, and return a 1 for a date. Although I give this out as a short answer, I will still contiue on the logic (at least until the roast is done!)

1
Blocked Profile
Nov 2, 2015 at 05:03 PM
OK, hang in there! Why don't you put all of the "NA" checks into one OR statement?

Like so:

=IF(OR(A4="",A4="NA",A4="N/A",A4="n/a",B4="",B4="NA",B4="N/A",B4="n/a"),0,1)

0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015
Nov 2, 2015 at 05:18 PM
Thank you for your help.

I tried the formula you suggested and it just returns the value of zero. I want this to happen if there is a blank or "NA" in both cells but if there is a date than I want it to give a result of 1. But if both of the cells have a date I want it to add those two 1's and give a result as 2. I might have to add more rows later so I wanted it to be able to add all the cells that would have a date in it.

Thank you
0
Blocked Profile
Nov 2, 2015 at 05:25 PM
OK, I have some short stuff to do, but I wil pick this up again shortly, and hopefully can condense it into one cell!
0

Didn't find the answer you are looking for?

Ask a question
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015
Nov 3, 2015 at 10:50 AM
Thank you for your help I will put it into two cells for now it did work. I will look forward to your revised answer

Thank you very much it did really help I never thought to split it into two cells.
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Nov 3, 2015 at 11:52 AM
Hi Dkayeh1

Why don't you put both formula's in one cell with a "+" in between like:

=IF(OR(B78="",B78="NA",B78="N/A",B78="n/a"),0,1)+IF(OR(B87="",B87="NA",B87="N/A",B87="n/a"),0,1)

Does that get you the desired result?

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0
What if the user enters "na" or "n/A" or "N/a" or some other odd combination of letters or characters?
If you set up 'Data Validation to only allow dates or blanks then you'll only have to check for blank values.
0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > RayH
Nov 3, 2015 at 12:40 PM
Thank you RayH for that suggestion.

I would do that if the database I created already wasn't so big. But I will keep that in mind when I create the next one.

So again thank you for that suggestion wish I would of thought of it in the beginning.
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Nov 3, 2015 at 12:01 PM
That is a good point RayH, didn't think of that!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 3, 2015 at 12:18 PM
Here is one more approach. If the cell truly contains date then you can use ISNUMBER functions

=IF(ISNUMBER(B78),1,0)+IF(ISNUMBER(B87),1,0)
0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Nov 3, 2015 at 12:36 PM
Thank you rizvisa1 for your input on this question as well.

The cell does contain a date but there are some cells throughout the workbook that does not have a date in those cells. Or unfortunately some user inputting the data put "n/a" because there was not a date listed in the file. So I needed a formula that would look at those cells and use a zero in the place of blank or n/a so it would calculate the formula without sending me an error message.

So thank you I can use this formula for something else though.
0