Returning value based on date

[Phil_L]

I am after a formula which I think would be very simple, but I cannot for the life of me find. So thank you in advance if someone replies.

Basically,

When someone types a date in A1, B1 would return a value (1 for example), if there is nothing in A1, B1 would be blank. If any other text is written in A1, it returns NA.

Hope this makes sense.

I currently have =IF(ISNUMBER(A1),1,"") in the B1 cell and that works ok. I just cant figure out what to put at the end if any other text is entered to display N/A.
(I suppose there is a better way of doing it)

Again, thank you in advance

Phil

 

[NateSC]

The structure of the IF statement is so that you have the test clause, the result if the test is true, and the result if the test is false. So all you need is to change the "" after the last comma to a "N/A".

=IF(ISNUMBER(A1),1,"N/A")

But be aware that the ISNUMBER() will test TRUE and give you a 1 in column B if column A has a regular number like 123 that isn't a date. I'm not sure if that is a possibility or a concern with your sheet.

 

[Phil_L]

Thank you for your reply.
You are on the right track but I think you misunderstood. Your answer would return N/A if nothing was entered in A1. That's not what I need. If there is no entry in A1, I need B1 to be blank. If A1 had ANY other text in there other than a date, I need it to return N/A, if there was a date, I need it to return the number 1

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(A1="","",IF(ISNUMBER(A1),1,"N/A"))

 

[Phil_L]

Perfect, thank you so much

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[NitchieF]

I'm not sure if this is proper etiquette here, but the string showed up on my search. I too would like to have a cell default to a formula based on one, an empty cell =IF(ISBLANK(J4), " ",, and two, would like it to default to Then (C2<TODAY()-20,"DUE","PENDING"). I'm struggling, new to this.. i was actually able to get the Cell to put forward two responses in the same cell, however I need then to work in succession not simultaneously.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(J4="","",IF(C2<TODAY()-20,"Due","Pending"))

 

[NitchieF]

Hi & welcome to MrExcel.
How about

=IF(A1="","",IF(ISNUMBER(A1),1,"N/A"))

looks good, let me try it,

 

[NitchieF]

Hi & welcome to MrExcel.
How about

=IF(J4="","",IF(C2<TODAY()-20,"Due","Pending"))

that did it, in the main, I added a couple of text inputs "y", "completed" and it worked great... thx so much.