Formulas

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.

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…