Dynamic View & Dates (in Text Format)

Options
cabeaudoin
cabeaudoin ✭✭✭✭
edited 07/26/23 in Formulas and Functions

I have set-up a Dynamic View (DV) filter using a helper column that converts a date to a text field display (this was required because DV doesn't allow filtering from a date field). I'm asking it to display items with a start date of today or less (so I can see all active items). Unfortunately some items are displaying that are outside of these parameters, specifically it appears it's ignoring the year component of the date:

The 2024 items called out above should not be included. You can also tell it's ignoring the year because it's alphabetizing by month, day. If it was acknowledging the year this would be listed at the end of this list. Any suggestions how to accomplish this?

Also, I'm having to manually update the date (see reference #1 in the first image). Is there a way I can have this always automatically display todays date? "TODAY" or "=TODAY()" doesn't work.

Tags:

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @cabeaudoin

    It's probably treating it as a fraction and ignoring the second forward slash and what comes after it.

    Can you set up another column to extract the year from that text date and then set up a second filter with an AND condition that looks at the year in the additional column?

  • cabeaudoin
    cabeaudoin ✭✭✭✭
    Options

    @Mike TV I will give that a try and report back. Thank you.

  • cabeaudoin
    cabeaudoin ✭✭✭✭
    Options

    @Mike TV

    This works, thank you. The only thing is this requires us to remember to remove this last filter when we need those dates to start being captured in the output. It would be great if there was a way so this wasn't required and it could automatically populate properly. I'm trying to automate this as much as possible. Having said that, greatly appreciate this workaround! Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!