Welcome!

If/Then

I am trying to program a cell to place the text "Exceeds 30 Days". I want this to be in the cell if todays date exceeds 30 days from the Project Completion date. I managed to do that just fine. However, if there is no date in the spread sheet for the job completion I do not want "Exceeds 30 Days" to populate. How do I accomplish this?

Find more posts tagged with

Comments

Dave L

Here is the form I am working on.

Dave L

Here is the form I am working on.

Luke X

Hi Dave,

The IF() function has three parameters, with the last one being optional. However, you can insert a blank string into the last parameter. The logic of this works with IF( **condition (i.e. is it more than 30 days?) **, **Show something if it is **, **Show Something if it isn't**). Try this (without the stars) and with " " in the last parameter instead!

Luke X

Mike Wilday

It sounds like you might also be checking other parameters as well. You can also manually check if the due date is blank. Using ISBLANK. The following formula is used on a row 24 and is lacking your column title but it will check to see if the column is blank and force a blank. Make sure the column is a text/number type column.

=IF(TODAY() >= [Column Title]24 + 30, "Exceeds 30 days", IF(ISBLANK([Column Title]24), ""))

Dave L

I do not understand what your saying. This is how I currently have it worded. But when I do this, it populates Exceeds 30 Days under report status even if there is no date for Project end date. I would like it to not put Exceeds 30 Days if there is no project end date.

=IF([Todays Date]11 > [Project End Date]11 + 30, "Exceeds 30 Days", "")

Dave L

Everything works except it is still putting the Exceeds 30 Days into the report status.

Mike Wilday

Try putting this formula in a separate date column to test.

=[Project End Date]11 + 30

Then compare the date result to today to see if perhaps today is actually 30 calendar days past the due date.

That's the only reason it would appear... especially if you're using the ISBlank([Project End Date]11, "").

Dave L

Here is how I have the formula typed in.

=IF(TODAY() >= [Project End Date]12 + 30, "Exceeds 30 days", =IF(ISBLANK([Project End Date]12), ""))

It is still putting the Exceeds 30 Days even when I have not entered a date for the Project end. I need the Exceeds 30 Days to not populate when I do not have a End Date.

Mike Wilday

Try it like this:

=IF(ISBLANK([Project End Date]12), "", IF(TODAY() >= [Project End Date]12 + 30, "Exceeds 30 days", ""))

This will put your first criteria checked as if it's blank.

Dave L

Now it is working as far as not populating when there is no end date, but it doesn't populate when there is a date that is exceeding 30 days. Is there something missing that tells it to perform the second part of the equation when the first condition is not met.

Mike Wilday

No that should work for sure. Can you try plugging in an end date that is over 30 days in the past? It should prompt you with the "Exceeds 30 days".

Also, make sure your row numbers are accurate. I used the example 12, cause that's what you provided, but if you are putting that in a different row than 12, it should have that row's number.

Can you upload a screenshot? That might help us troubleshoot.

Dave L

Mike Wilday,

Thanks for your help. The last formula actually worked. I must have done something wrong the first time I tried it. I appreciate the help.

Dave

Mike Wilday

You're welcome!

Sign in to join the conversation!

Quick Links

Trending discussions

Not enough space in the cell for my notes
Hi, I'm using smartsheet to track updates on each task in a tracker. These updates may span over a year, so they get lengthy. I was excited about smartsheets because viewing these notes is a lot cleaner. However, I've just realized that there is a content limit to a cell, which is a huge issue. While excel was annoying to…
how can i overlap 2 tasks.
Hello, i've a question about 2 tasks. Task 1 has for example 4 hours and should be on day 1 for example 6 january. Task 2 has 30 hours and should be on start on 4 january and futher. How can i manage that on 6 january task 2 has only 4 hours? Now i got the message user is over-allocated because task 2 is planned on a full…
Update Automations
Hi All, We have several update automations on various solutions - and have worked very well for getting info from people that dont have smartsheet access - how ever in Sept we switched over to SSO and MFA and activated the Safe Share - We have now run into an issue that when our update automations trigger and people within…