Using IF function with dates

Question

Using IF function with dates

Solved/Closed

Report

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.

Using IF function with dates
How do you do an if function with dates? - How-To - Excel
IF functions with dates ✓ - Forum - Excel
Some help needed about IF function with dates ✓ - Forum - Excel
Using the IF function for dates ✓ - Forum - Excel
IF function using dates ✓ - Forum - Excel

6 replies

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)

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

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!

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.

Answer 6 / 6

TrowaD

Posts: 2829
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: November 22, 2021

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

View all 12 comments

Report

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768 > dkayeh1

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

Nov 3, 2015 at 03:06 PM

Even though I dont think you need it, but i think it would help you or some one in understanding the logic of the the formula.

in excel date is stored internally as NUMBER. so if you have a cell that has a date, then basically it is a number. ISNUMBER is checking if the cell has a number or not. so any thing that is not a number (blank, -, NA, n.a, N/A etc) will not be considered as number. Hence IF(ISNUMBER(B78),1,0) is testing if B78 has a number (date or a number), then you get 1 else u get 0. On flip side, if user has entered a number in B78, then it would evaluate as true and u will have not expected result. If the user has entered a text that looks that date but is considered by excel as date ( '1/1/2015 looks like a date but is a text due to presence of ') it would evaluate as false
,

Report

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

Report

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

Nov 3, 2015 at 01:57 PM

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.

Report

Rayh
Nov 3, 2015 at 06:37 PM

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.

Report

dkayeh1

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

> Rayh
Nov 4, 2015 at 10:44 AM

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

Report

RayH > dkayeh1

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

Nov 4, 2015 at 12:42 PM

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.

Ask a question

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.
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.
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)
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.
Even though I dont think you need it, but i think it would help you or some one in understanding the logic of the the formula.

in excel date is stored internally as NUMBER. so if you have a cell that has a date, then basically it is a number. ISNUMBER is checking if the cell has a number or not. so any thing that is not a number (blank, -, NA, n.a, N/A etc) will not be considered as number. Hence IF(ISNUMBER(B78),1,0) is testing if B78 has a number (date or a number), then you get 1 else u get 0. On flip side, if user has entered a number in B78, then it would evaluate as true and u will have not expected result. If the user has entered a text that looks that date but is considered by excel as date ( '1/1/2015 looks like a date but is a text due to presence of ') it would evaluate as false
,
Hello, rizvisa1 I do understand better, but unfortunately I can not use it because having temp help enter data there is always the possibility that they would just enter numbers accidentally erase my criteria for date etc.

But thank you for the explanation of what it looks at in the cell I know it will help all of us to understand better.
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
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.
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
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.

Blocked Profile · Accepted Answer · 2015-11-02T17:52:23-0500

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

Using IF function with dates

6 replies

Your reply

Subscribe To Our Newsletter!