PowerAutomate Microsoft Form Submission Date to Smartsheet Date Column

Cristina Hanson
Cristina Hanson ✭✭
inFormulas and Functions

Good Morning,

Hoping someone might provide some expertise on a date conversion issue I'm having. I have a Microsoft PowerAutomate set up to bring Microsoft Form responses to Smartsheet and create a new row. I would then like to identify forms submitted which =Today() give it a 1, then that is used in other columns which track responses for Today only. Originally I built the comparison against the create date of the row =IF(Created@row = TODAY(), 1, 0) which works fine for part of the day, but not all of it because I believe its basing it off of Smartsheet server time and not my local EST time. In the screenshot below you can see where some create dates are 12/14 and some 12/15, but the formula is saying both are equal to Today.

There is an option in PowerAutomate to bring the date and time from the form into the sheet, but I can't seem to find the right date format that the Smartsheet date column will accept. I've tried at least 5 of the below and each time it says errors out my automation because Smartsheet doesn't recognize the value as a date.

There is a screenshot below of what is available for MS date formatting, or it has an option for custom value for the formatting.

Hoping someone can provide insight on either of the below. Thank you so much for your help!

1) the right format to put in PowerAutomate for my date column to accept (easiest) or

2) formula to give help me achieve the 1 for = Today()


image.png
image.png


· Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi @haliecarr<\/a> ,<\/p>

Give this a try.<\/p>

=SUMIFS({Broker Report Test 2 Range 1}, {Broker Activity Report Range 6}, \"January\", {Broker Activity Report Range 1}, OR(@cell = \"EMORY DECATUR I\",@cell = \"EMORY DECATUR II\",@cell = \"EMORY DECATUR III\"))<\/p>

Hope this helps,<\/p>

Dave<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111186,"type":"question","name":"Dividing to Create a %","excerpt":"I would like to use the data in Column A and B to create a percentage complete by row NOT by parent\/hierarchy. In Excel this would be =sum(Column B\/Column A) = %. What is the formula for Smartsheet?","snippet":"I would like to use the data in Column A and B to create a percentage complete by row NOT by parent\/hierarchy. In Excel this would be =sum(Column B\/Column A) = %. What is the…","categoryID":322,"dateInserted":"2023-10-04T15:20:08+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T18:59:48+00:00","insertUserID":78761,"insertUser":{"userID":78761,"name":"tmkj20","url":"https:\/\/community.smartsheet.com\/profile\/tmkj20","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!gnPeZ7gLMjw!!EdcH89Wp7wc","dateLastActive":"2023-10-04T17:29:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-04T23:32:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":31,"score":null,"hot":3392881196,"url":"https:\/\/community.smartsheet.com\/discussion\/111186\/dividing-to-create-a","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111186\/dividing-to-create-a","format":"Rich","lastPost":{"discussionID":111186,"commentID":398450,"name":"Re: Dividing to Create a %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398450#Comment_398450","dateInserted":"2023-10-04T18:59:48+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-04T23:32:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-04T17:30:15+00:00","dateAnswered":"2023-10-04T16:17:20+00:00","acceptedAnswers":[{"commentID":398399,"body":"

You would just use<\/p>

=[Column B]@row \/ [Column A]@row <\/p>


<\/p>

EDIT: Then apply the percentage format to the column.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111135,"type":"question","name":"Using IF\/AND with two ranges of data.","excerpt":"I have been working on the following problem for a couple of days, but I have not been able to get anything even remotely correct to work as desired. I can only get anything to execute if I tie the two lookup cells to row #. =IF(AND(Adresses@row = Streets1, Include1 = \"Yes\"), \"Yes\",\"No\") That is obvious not what I want!…","snippet":"I have been working on the following problem for a couple of days, but I have not been able to get anything even remotely correct to work as desired. I can only get anything to…","categoryID":322,"dateInserted":"2023-10-03T17:50:14+00:00","dateUpdated":null,"dateLastComment":"2023-10-05T05:56:11+00:00","insertUserID":30576,"insertUser":{"userID":30576,"name":"Teamciccone30576","url":"https:\/\/community.smartsheet.com\/profile\/Teamciccone30576","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T05:56:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":30576,"lastUser":{"userID":30576,"name":"Teamciccone30576","url":"https:\/\/community.smartsheet.com\/profile\/Teamciccone30576","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T05:56:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":32,"score":null,"hot":3392845585,"url":"https:\/\/community.smartsheet.com\/discussion\/111135\/using-if-and-with-two-ranges-of-data","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111135\/using-if-and-with-two-ranges-of-data","format":"Rich","lastPost":{"discussionID":111135,"commentID":398543,"name":"Re: Using IF\/AND with two ranges of data.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398543#Comment_398543","dateInserted":"2023-10-05T05:56:11+00:00","insertUserID":30576,"insertUser":{"userID":30576,"name":"Teamciccone30576","url":"https:\/\/community.smartsheet.com\/profile\/Teamciccone30576","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-05T05:56:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/AQJHUDLOJCEX\/screen-shot-10-03-23-at-11-14-am-jpg.jpg"},"alt":"Screen Shot 10-03-23 at 11.14 AM.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-05T05:56:33+00:00","dateAnswered":"2023-10-05T05:56:11+00:00","acceptedAnswers":[{"commentID":398543,"body":"

@Joe Calderazzo<\/a><\/p>

I guess you helped me with the checkbox question as well.<\/p>

I used the exact same formular on the sheet, now with Checkbox columns, and it works perfectly.<\/p>

And when I change the “Include” column selection, it changes the “Mailing list\" instantly.<\/p>

Exactly as I wanted it to work.<\/p>

 <\/p>

I cannot thank you enough for getting me on the right course with this.<\/strong><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Can you use OR in a SUMIFS formula

    I have a sheet full of data from a form that is sent out each month to multiple groups of people regarding multiple different properties. I have 4 quantifiable questions on the form that I have been using the following formula on to collect data. Example below =SUMIFS({Broker Report Test 2 Range 1}, {Broker Activity Report…

  • Dividing to Create a %

    I would like to use the data in Column A and B to create a percentage complete by row NOT by parent/hierarchy. In Excel this would be =sum(Column B/Column A) = %. What is the formula for Smartsheet?

  • Using IF/AND with two ranges of data.

    I have been working on the following problem for a couple of days, but I have not been able to get anything even remotely correct to work as desired. I can only get anything to execute if I tie the two lookup cells to row #. =IF(AND(Adresses@row = Streets1, Include1 = "Yes"), "Yes","No") That is obvious not what I want!…