Format Date

[rodders122288]

Hi all, Sorry I can't find an answer to this anywhere. Apologies if there is one.

So I have an Excel table where I want the column header to report a Month but understand that a table header can not display a date / month as a field so I have entered a date in the next row down but this is coming back unformatted.

If I try to format it it the whole table then formats which is wrong.

How can I get my PA to show me the date

p.s. I'm quite new to PA so be gentle with me 🙂

[Nived_Nambiar]

Hi @rodders122288 

is the below table in screenshot is part of excel table itself ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[rodders122288]

Hi, Not sure the pictures have worked well, I have attached again

Top one is the table in excel and the other is the output from my flow

[Nived_Nambiar]

Hi @rodders122288 

could you show how your flow looks like ?

Also specifically show the properties of List rows in table action 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[rodders122288]

Is this enough ?

[Nived_Nambiar]

Hi @rodders122288 

Try to select the Date Format option as ISO 8601 as shown below in List rows in table action

The numbers which are shown in your current flow's output instead of date is actually the number of days by which the date given is from 1st Jan 1900

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[rodders122288]

Thanks, 

Tried that already 😞🤣

[David_MA]

I am not exactly sure what you are doing, but it looks like you are seeing Excel serial dates. To convert this to the ISO 8601 date format, you would use this expression: 

addDays('1899-12-30',int(triggerBody()['excel_value']),'dd-MM-yyyy')

DATEVALUE function - Microsoft Support

[Nived_Nambiar]

Hi @rodders122288 

then you have to apply formatDateTime function over that the dynamic content so to convert the dates to desired format.

see an example below to use formatDateTime

formatDateTime(item()?['1 Month'],'yyyy-MM-dd')

do the same for other columns

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[rodders122288]

Hi

I am only trying to format the months May June July Aug to read the months and if I add ISO 8601 it formats all the numbers below as well

[rodders122288]

Thanks

But where do I put that ?

[David_MA]

Just change the last part of the expression 'dd-MM-yyyy' to return the format you need. I use this reference for the various options: formatDateTime in Power Automate in 2024 (sharepains.com)

[Nived_Nambiar]

Hi @rodders122288 

already updated in my answer

Also the expression which i have written 

formatDateTime(item()?['1 Month'],'yyyy-MM-dd')

instead of item()?['1 Month'] pass the dynamic content which you are passing in create html table now 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[rodders122288]

Flow fails

InvalidTemplate. The execution of template action 'Create_HTML_table' failed. The column values could not be evaluated: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

[Nived_Nambiar]

Hi @rodders122288 

You have to pass the highlighted dynamic content to the formatDateTime function 

not the item()?['Month 1']

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[rodders122288 · Accepted solution]

All, Thanks for your ideas

I fixed it by using the below formula in the cell the Month was showing so it reported back as text and not a number

=TEXT($W3,"MMM")