Formulas

justin46491

I have a formula that is working in most of the cells.  However the number is not showing and the formula is in the cell.  I am not sure as to why it is not showing up with a value.

Here is a copy of the formula.

 

=IF([project name]1 = "", "", IF([PanAm proposal needed by]1 = "", JOIN("it's been ", TODAY() - [quote request by customer]1, " days"), IF([PanAm proposal for customer]1 - [PanAm proposal needed by]1 < 0, JOIN(-([PanAm proposal for customer]1 - [PanAm proposal needed by]1), " days to spare"), JOIN([PanAm proposal for customer]1 - [quote request by customer]1, " days overdue"))))

 

Need Assistance.

Jeremy C

Justin - I just tried this real quick and the formula looks to work. Are your columns set to dates?

Jeremy C

Justin -  I don't believe you need the joins in this formula. I think you can accomplish the same thing using the following format:

=IF([Project Name]1 = " ", " ", 

IF([PanAm proposal needed by]1 = " ", "it's been " & TODAY() - [quote request by customer]1 & " days")....

Also I would make sure when referencing a blank cell to add a space between the quotation marks. Hope this helps.

justin46491

I tried doing this and it was not working and I got the Unparsable again

Jeremy C

Sorry about that, replace the & with +.

IF([PanAm proposal needed by]1 = " ", "it's been " + TODAY() - [quote request by customer]1 + " days")....

justin46491

It is still not working.

Jeremy C

Are your column types for PanAm proposal needed by, quote request by customer, and PanAm proposal for customer set as dates? What error are you getting and does it apply to all rows you are applying this formula to? I've attached a screenshot of the mock sheet I'm using to trouble shoot.

justin46491

Yes they are set to Date

 

justin46491

Yes they are labeled that.  When I do the formula I am getting they unparsable error and it does apply to all rows.

 

What is the formula you are using then?

Jeremy C

Hope the screenshot makes sense.

=IF([Project Name]1 = "", "No Proj Name", 

 IF([PanAm proposal needed by]1 = " ", "it's been " + TODAY() - [quote request by customer]1 + " days", 

 IF([PanAm proposal for customer]1 - [PanAm proposal needed by]1 < 0, [PanAm proposal for customer]1 - [PanAm proposal needed by]1 + " days to spare", 

 [PanAm proposal for customer]1 - [quote request by customer]1 + " days overdue")))

justin46491

Now it works perfectly.

 

Thank you so much