Previous Topic Next Topic

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.
Related:

6 responses

Answer 1 / 6
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
Answer 2 / 6
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
Answer 3 / 6
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
Answer 4 / 6
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
Answer 5 / 6
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
Answer 6 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
RayH
Nov 3, 2015 at 11:58 AM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 3, 2015 at 12:01 PM
That is a good point RayH, didn't think of that!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
Nov 3, 2015 at 12:32 PM
Thank you very much TrowaD. I used the formula and it worked great; it did exactly what I wanted it to do.

Thank you for all of your help
0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > rizvisa1 Posts 4478 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

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Dell laptop FN key is not working
adibah -
Artur -

93 responses
USB:\ is not accessible. Incorrect Function.
rickitikz -
Amar Yadav -

7 responses
Excel function question
1234ah -
ac3mark -

3 responses