Power automate can't pull display value of a date column formatted cell?

Nate Ensor

Hello -

Two part question here.

1. Trying to pull row data from SS using Power Automate to then send an email using that data. Works for every cell except the date formatted columns when variables are string and set to pull display value. So is there a way to get power automate to recognize the data in a SS date column?
2. One of the workarounds I'm trying is to use a formula (in SS) in a text cell to pull the date in as text. But that is proving tricky as well. I don't need it to be in anything other than 8/27/24 text format so that power automate will populate the email correctly.

Any ideas? Preference is a fix for #1, but I'll take #2 as well. thanks!

Kelly Moore

Hey @Nate Ensor

For your #2, converting a date to text, in case you meant that as the tricky part.

=[date column]@row+""

You might also try using format yyyy-mm-dd to see if that helps

Will that work for you?
Kelly

PS - as an alternative workaround - have you considered having smartsheet email you the data then you power automate it from the email you received? Not knowing your data or email, I'm not sure if it would be easier or not

Nate Ensor

@Kelly Moore

Thank you. Formula is perfect. I was definitely overthinking it.

I have thought about building the flow in power automate via a smartsheet email…but I already did the entire thing this way so I'm trying to see if there is a way to have the SS ate field recognized or converted so it will populate in the html before I redo the entire thing. :(

When you mentioned "using format yyyy-mm-dd" is that something I can do in SS? or are you talking Power automate?

Kelly Moore

@Nate Ensor

There is a data format in SS that will format as yyyy-mm-dd, however that format doesn't 'stick' as the data is isn't really stored that way. I do know as I've transferred data into SS from excel, etc, that I've sometimes had to change the SS format to yyyy-mm-dd to obtain the correct data. If you wanted to try the text conversion into yyyy-mm-dd then here's the formula that will convert your DATE column to that text format

=IF(ISDATE([your date column]@row), 2000 + VALUE(RIGHT([your date column]@row, 2)) + "-" + LEFT([your date column]@row, 2) + "-" + MID([your date column]@row, 4, 2))

Good luck! Hopefully our smarter smartsheet friends out there will weigh in.

Kelly

Nate Ensor

@Kelly Moore

Thanks, I'll give it a try if your previous formula doesn't pull into the email correctly.

Still holding out hope someone will have the key for power automate so I don't need an extra column for every date column.