Using IF function with dates [Solved/Closed]

Report
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
-
 RayH -
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


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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4279 users have said thank you to us this month


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)

Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015

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

OK, I have some short stuff to do, but I wil pick this up again shortly, and hopefully can condense it into one cell!
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015

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.
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
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.
dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015

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
dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
> dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015

Hello TrowaD I used the formula that you suggested it worked but I seem to be having a problem to where it is added the zeros. For instance I have six rows that have a zero in it and it gives me the result of 6. Is there something I am missing I copied the formula below:

=IF(OR(B105="",B105="NA",B105="N/A",B105="n/a"),0,1)+IF(OR(B114="",B114="NA",B114="N/A",B114="n/a"),0,1)+IF(OR(B123="",B123="NA",B123="N/A",B123="n/a"),0,1)+IF(OR(B132="",B132="NA",B132="N/A",B132="n/a"),0,1)+IF(OR(B141="",B141="NA",B141="N/A",B141="n/a"),0,1)+IF(OR(B150="",B150="NA",B150="N/A",B150="n/a"),0,1)

Thank you for your help it is greatly appreciated.
I think this is getting way too complex.
Create a function in a module:

Function ChkIfDate(c As Range) As Integer
If isdate(c) Then ChkIfDate = 1 Else ChkIfDate = 0
End Function


then in the fields you want to see the count of dates put this, e..g.

=chkifdate(N2)+chkifdate(N12)


This way you wont have to worry about any spurious characters the temp might enter by mistake and it looks a whole lot cleaner.
dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
> Rayh
Hello Rayh thank you for your help. I like the idea you gave here to create a module in vb but that is not one of my strong suites. I tried to create it but it will not work can you give me step by step instructions on how to create, what goes on what line and to save it so it works. All help is so appreciated because I am getting a headache trying to get this to work with all type of users.

Thank you very much
> dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015

To bring up the editor, press Alt-F11
Right click on your VBAProject
Click Insert
Click Module
Paste in the 3 lines of function code.
Close the window

In the Excel worksheet, put the formula in the cell where you want to the number of dates to be.

The example of =chkifdate(N2)+chkifdate(N12) is looking at cells N2 and N12 to see if they are dates. Replace these cell references with B105, B106 etc.
The function will return a 1 if a date is found or 0 if it is not.