nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Bienvenue !

This Post is Archived and Read-Only

The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.

Browse Active Topics

Return a value based on a date

Admin@PAT

Hi there everyone,

I have posted before but am still stuck.

https://app.smartsheet.com/b/publish?EQBCT=5b60441a73dc4bfe9c4e77977e0cce7c

Hope this is helpful and clear.

Réponses

Benjamin Walker

Your publish link did not work for me.

Admin@PAT

https://app.smartsheet.com/b/publish?EQBCT=5b60441a73dc4bfe9c4e77977e0cce7c

Hope this works, looks the same to me?

Admin@PAT

Is there an IF/AND formula I can create???

I get this far, then my nesting simply doesn't work: =IF([Date Expected]11 < $[Date Expected]$1, "Overdue")

Benjamin Walker

You need to add your next IF() statement after the "Overdue"

Admin@PAT

Thanks Ben,

Yes, I have done that - and get an error in my formula - my next 'statement' requires to look between 2 dates.

=IF([Date Expected]11 < $[Date Expected]$1, "Overdue"

Statement would be: If Date Expected11 is 'between' Date Expected 1 and Date Expected 2, "HOT" (and two more of these statements to look at date ranges and return Hot, Medium or Cold.

I cannot work out the 'between' logic in the formula. Does smartsheet support AND logic?

=IF([Date Expected]11 < $[Date Expected]$1, "Overdue"), IF(AND([Date Expected]11 > $[Date Expected]$1, < $[Date Expected]$2 "HOT")

This formula give UNPARSEABLE result... I just can't work it out... hmmmph! I am missing a 'link' between the individual statements. The sheets shows what I am trying to achieve.

Benjamin Walker

Looks like you had a couple parenthesis missing and needed to supply the second operator for the AND! I fixed it on your sheet.

Here is the formula I came up with:

=IF([Date Expected]11 < $[Date Expected]$1, "Overdue", IF(AND([Date Expected]11 > $[Date Expected]$1, [Date Expected]11 < $[Date Expected]$2), "HOT", "COLD"))

Hopefully with that you can continue building your formula. This link may help you:

https://help.smartsheet.com/articles/775363-using-formulas#logic