Help! Formula: DATE

Platform discussions
1

I have a formula to calculate & set a date specific to the status that the item’s at.

e.g. if Status = A, then date is ‘1 Jan 2023’, if Status = B, then date is ‘13 Feb 2023’ and so on.

am guessing it’s a combo with formatdate - anyone found an answer.

but it shows up as a text and not a date that I can take action on for instance, sending alerts and notifications via automations.

given that we can’t parse values that are not in any columns…your wizardry will help!

Been struggling with setting up the date/timeline thing for 4 weeks now and so far my solutions were

  1. too cost prohibitive (3rd party app) i.e. low ROI
  2. mirrored timelines isn’t recognised for automations/formula
  3. status-specific formulas turned off the automations when activated (team at monday’s on it)
  4. I can’t do anything with the date in the formula (above)
2

Hi Jenny
Hope all is well

Hopefully this gives what you need
It formats the date into a date format based on the status selection

IF({Status for date}=“A”,FORMAT_DATE(“2022-11-16”,“D-MMM-YY”),IF({Status for date}=“B”,FORMAT_DATE(“2022-11-28”,“D-MMM-YY”),IF({Status for date}=“C”,FORMAT_DATE(“2022-11-30”,“D-MMM-YY”),“”)))

If you need any additional help, just let me know
Many thanks in advance
Dan

Screenshot 2022-11-16 at 11.36.12

2 Likes
3

Thanks @DanIngham-IOI I’ll give this a go!

1 Like
4

Hey Dan! @DanIngham-IOI , I’m looking for a formula if a TEXT column field has data in it to set a date for today?..is there a way?..i’ve not figured out the proper syntax to use but i’m playing around and could use some knowledge…thanks!

5

Hi Rich,

Since formulas are calculated dynamically, today’s date would change every day. If your idea is to store the date text was entered (and freeze it forever), this won’t work.

You could use an automation like this (The 3rd line is only necessary if you don’t want the date to be updated every time someone edits the text):

2024-02-08_18-50-07

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.

6

@GCavin yes, exactly what i’m seeing the date writes over and over. So I tried your suggestion and it works better, however, can I also do the converse that if someone removes the text AND the text is empty how can I remove the today date?..yea, its like a reset? otherwise i will end up with mismatch of data columns especially these dates.
example that I’m testing but its not working:

When TEXT changes
and if TEXT is empty
and if DATE is empty

Then ???

I tried this… “Then clear COLUMN” and that didn’t work…how can i remove the date if the text was removed?

Thanks very much!

7

Create another automation:

2024-02-09_08-15-33

Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com*

8

works…Thanks Gilles! :+1: