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