Date time challenge & Nested Ifs formula solved?

[Steve_Vo]

How would you write a date time formula for Beginning of current month to End of current month? The trick is, it’s the current month and the month always changes.

Any Jedi out there?

[Steve_Vo]

Hi,

I have revised the formula with an extra & . It’s not working, What’s missing?

IF(FIND(‘App Set-AL’,{Delivery Co-ordinator Activities}),‘App Set-AL’,&IF(FIND(‘Books Done-AL’,{Delivery Co-ordinator Activities}),‘Books Done-AL’,&IF(FIND(‘Veh Checked-AL’,{Delivery Co-ordinator Activities}),‘Veh Checked-AL’,&IF(FIND(‘Delivered-AL’,{Delivery Co-ordinator Activities}),‘Delivered-AL’,&IF(FIND(‘MotorOne-AL’,{Delivery Co-ordinator Activities}),‘MotorOne-AL’,&IF(FIND(‘CANP-AL’,{Delivery Co-ordinator Activities}),‘CANP-AL’,&IF(FIND(‘A/M on Del-AL’,{Delivery Co-ordinator Activities}),‘A/M on Del-AL’,&IF(FIND(‘MyToyota-AL’,{Delivery Co-ordinator Activities}),‘MyToyota-AL’))))))))

[Kamille_Parks]

You using several nested if statements instead of two or more separate if statements separated by an ampersand.

[Steve_Vo]

Hi,

I’m not sure how to write the formula to make it work? Can you please tweak it?

IF(condition, ‘value if true’)&IF(condition, ‘value if true’)

IF({Delivery Co-ordinator Activities},‘App Set-AL’,‘Delivered-AL’)&IF({Delivery Co-ordinator Activities},‘App Set-AL’,‘Delivered-AL’)

[Justin_Barrett]

The way you’ve written it, by putting only the field name into the “condition” part of the IF function, it will check to see if anything is in the {Delivery Co-ordinator Activities} field. The “condition” part should be the full FIND syntax you were using before, looking for specific text, and outputting that text if found (the “value if true”).

Long story short, the change you need to make to your tweaked version is to close each IF before the next IF begins, instead of nesting them inside each other. End the IF with the closing parenthesis, add an & to concatenate that IF’s result with the next one, then write the next IF as a standalone piece. Lather, rinse, and repeat. Here’s the beginning restructured in that way:

IF(FIND('App Set-AL',{Delivery Co-ordinator Activities}),'App Set-AL')
& IF(FIND('Books Done-AL',{Delivery Co-ordinator Activities}),'Books Done-AL')
& IF(FIND('Veh Checked-AL',{Delivery Co-ordinator Activities}),'Veh Checked-AL')
...

Does that make the setup more clear?

[Kamille_Parks]

Follow @Justin_Barrett’s example.

This post no longer has anything to do with its title. Can you rename it to reflect the 4 or so rounds of questions? This will make it easier for other users if they have similar questions to find the answer.

[Steve_Vo]

Hi!

Just want to report that’s everything is working like a Swiss Watch, thanks everyone!

[Steve_Vo]

Hi,

Need some help with this If statement. Currently: IF({Payment}=“Outside Finance”,CREATED_TIME())
What I would like to do is " If ({Payment}=“Outside Finance”,Create-Time(), And if when {Payment} changed from “Outside Finance” to “something else”, leave the original time created.

Thanks for your help in advance!

[Kamille_Parks]

Steve_Vo:

Need some help with this If statement.

This should be its own post.

Assuming your {Payment} field shows sequential steps in a process your formula could be IF(OR({Payment}='Outside Finance', {Payment}='whatever comes after outside finance',{Payment}='etc'),CREATED_TIME())

[Justin_Barrett]

Steve_Vo:

What I would like to do is " If ({Payment}=“Outside Finance”,Create-Time(), And if when {Payment} changed from “Outside Finance” to “something else”, leave the original time created.

Unfortunately formulas don’t work that way. There’s no way to tell a formula to “freeze” on a certain value once a condition is met. It will always evaluate based on whatever triggers feed it, so if any related field changes, it’s gonna change.

If the suggestion from @Kamille_Parks won’t work, the only other way to do this is to tie in a service like Zapier or Integromat, and change {Outside Finance} from a formula to a date field, letting the integration service populate the field with the proper date based on the rules you set.

[Steve_Vo]

Thanks for your reply. I have found a work around where I changed the field to a multi-select and this will record the initial status and also what changes after, thanks!