"Compose" and "Get a Row" using DateTime Format will not return ISO

[rmccracken]

When a MS form is submitted, I'm adding a row to an Excel sheet. From there, formulas are calculating various rows (including a few dates) automatically depending on the form submission date. I am then using "get a row" to get those calculated values to include in a confirmation email which combines dynamic values from the Form submission and the Excel calculated values. It works except the dates are returning serial numbers and not ISO no matter what I try. Here's what I've tried:

• Creating the whole flow again just in case it was a glitch.
• Ensuring "DateTime Format" in "Get a row" advanced options says "ISO 8601". This still returns the serial number. 
  
  Example of results:
  
• Using addDays('12-30-1999', int(item()['45453.5980092593']), 'MM-dd-yyyy')  in a Compose action. This just enters the full formula in the email body.
  
  Example of results:
  
• Using formatdatetime([45453.5980092593],'MM-dd-yyyy') in a Compose action. Like the previous attempt, this just enters the full formula in the email body.

MY FULL FLOW

Dynamic content from the Compose step:

Dynamic content from Send an email step (these are obviously both wrong and I'd need to duplicate the correct answer but I wanted to demonstrate two wrong answers--lol).

 (Result in email = "addDays('12-30-1999', int(item()['45453.5980092593']), 'MM-dd-yyyy') ")

 (Result in email = "45459.5980092593")

Any help is VERY MUCH appreciated! Tried every possible solution I could find.

[creativeopinion]

@rmccracken You have a string entered here not an expression. Take a look at this section of my YT Tutorial: 

7 Functions You Need to Know | ⚡️Expression Essentials: Part 1⚡️

In this section, I'll cover how to get dynamic content with an expression when the dynamic content you need isn't listed in the dynamic content menu.

In the full tutorial I cover 7 functions you need to know when getting started with expressions.

1️⃣ empty()

2️⃣ coalesce()

3️⃣ equals()

4️⃣ if()

5️⃣ concat()

6️⃣ length()

7️⃣ split()

I cover how to use these functions in expressions and I’ll also cover common mistakes when it comes to writing expressions and show you a few tips and tricks along the way.

As a beginner or even an intermediate flow builder—expressions can seem a bit complex at first, I’m going to try to simplify it for you. If you want to level up your flows by writing expressions—keep watching!

IN THIS VIDEO:

✅ What is an Expression?

✅ What is a Function?

✅ What Does Wrapping a Function Mean?

✅ How Do I Insert an Expression?

✅ How to Use a Compose action

✅ How to Navigate the Expression Builder with Arrow Keys

✅ How to use the Expression Tooltip

✅ Common Mistakes When Writing Expressions

✅ How to differentiate a null from an empty string

✅ How to Get Dynamic Content When it’s Not Listed

✅ How to Use a Get Item Action to Verify Dynamic Content Output

✅ How to Convert Strings to Lower Case

✅ How to Troubleshoot the if() Function

-----

You may also be interested in this tutorial: How to Work with 📆 Dates in Power Automate | Example Scenarios and Tips & Tricks

Are you easily stumped when working with Dates in Power Automate? In this Power Automate tutorial, I’ll show you how to compose an expression that will return a future date, a past date, how to count the number of days between two dates, how to check for a birthdate and anniversary date as well as tips and tricks when working with dates in Power Automate.

I’ll cover some common use cases and concepts that can help you to build better Power Automate flows. Feel free to skip ahead using the timestamps listed below. I’ve also linked a few other tutorials that you might be interested in as well.

IN THIS VIDEO:

✅ 4 Date Functions You Need to Know

✅ How to use the Convert Time Zone Action

✅ How to Get a Future Date

✅ How to Get a Past Date

✅ How to Return SharePoint Items Due in a Number of Days

✅ How to Return SharePoint Items Due within a Date Range

✅ How to write a Filter Query for SharePoint Items Due in 30, 60 and 90 Days

✅ How to Calculate the Number of Days between Two Dates

✅ How to Check for a Birthdate and Anniversary Date

✅ How to work with Dates and Times in Excel

✅ How to use a Manual Trigger with Date Input

✅ How to Output Dynamic Text with an Expression

✅ How to Check if a Date Falls on a Weekend

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[rmccracken]

@creativeopinion  Thanks for your help and links. I'm obviously still missing a crucial part. 

I tried using inputting as an expression following a couple tips from your videos (making the column title without spaces; double-checking column is formatted as date; using expression in compose). I'm receiving the following error at compose step. 

Here's what I have:

I've also tried other versions of the expression and get "this expression is invalid" and won't let me save it at all. Thoughts?

[creativeopinion]

@rmccracken Can you remove the expression from the Compose action and insert the dynamic content for the date from the Get a Row action and run a test? The error is saying that the output of your date isn't in the correct format. You can confirm this by reviewing the output of the dynamic content. 

For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this Power Automate tutorial, I explore 5 frequently asked questions that pop up when troubleshooting a flow. If you’d like to to level up your Power Automate flow skills and learn how to troubleshoot your Power Automate flow—this tutorial is for you!

IN THIS VIDEO:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

[rmccracken]

I think this is what you've asked me to test. It generates the serial number in the email... which is the problem I'm trying the solve. No matter what I try, the date won't convert to ISO 8601... it stays as a serial.

Here's the inputs/outputs from the relevant steps:

...and then obviously the input and output is the same for the compose action.

[creativeopinion]

@rmccracken I couldn't replicate your issue on my end. Can you try to create a brand new flow and ensure you toggle off the New Designer before you make any edits to your flow. Add the Get a Row action and a Compose action. 

Set the DateTime Format to ISO8601. Run a test.

[rmccracken · Accepted solution]

The only thing I'm coming up with is that in Excel the date that is being returned as a serial is a calculated cell/column. Could that be it? 

I tried doing what you suggested of a flow with just Get a Row and Compose and same issue. Date returned from Get a Row is a serial number despite setting DateTime Format as ISO 8601.

I don't use the new designer so that's not the issue.

...
Get a Row output (relevant field):

....

With that suspicion, I just tested using this formula in Excel (added a new calculation) using this "sloppy" solution:

=TEXT([@[Email_Sent]], "MM/DD/YYYY")

AND THAT WORKED! I don't know why it worked. Feels like it shouldn't. But I'm crying happy tears to end this maddening saga.

Thanks for all your assistance @creativeopinion