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
RayH - Nov 4, 2015 at 12:42 PM
Related:
- Using IF function with dates
- Find function on mac - Guide
- Accessor function c++ - Guide
- Spreadsheet function - Guide
- Agp function - Guide
- Hard drive function - Guide
6 responses
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!)
OK, hang in there! Why don't you put all of the "NA" checks into one OR statement?
Like so:
Like so:
=IF(OR(A4="",A4="NA",A4="N/A",A4="n/a",B4="",B4="NA",B4="N/A",B4="n/a"),0,1)
dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
Nov 2, 2015 at 05:18 PM
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
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!
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
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.
Thank you very much it did really help I never thought to split it into two cells.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Nov 3, 2015 at 11:52 AM
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.
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.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Nov 3, 2015 at 12:01 PM
Nov 3, 2015 at 12:01 PM
That is a good point RayH, didn't think of that!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Nov 3, 2015 at 12:18 PM
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)
=IF(ISNUMBER(B78),1,0)+IF(ISNUMBER(B87),1,0)
dkayeh1
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
Nov 3, 2015 at 12:32 PM
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
Thank you for all of your help
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
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.
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.
