Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

Excel date string to integer

(0) ShareShare
ReportReport
Posted on by 2
Hi, I have a flow, which up until today has run fine but has suddenly stopped. The purpose of this flow is, in short, when a form is submitted, the information from an excel table is gathered, put into an html table and sent as an email. The issue that I'm having is that, one of the columns which is in the excel table is a list of dates, which if I leave as is and put into the html table, it goes in in a string. So up until now, in the select action, where I am putting in the dates from excel, I have used this formula addDays('12/30/1899',int(item()?['Date Checked']),'dd/MM/yyyy') (in this item()?['Date Checked'] is the dynamic content for the excel date). For some reason this is suddenly not working and the flow fails with this error 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.  . Does anyone have any idea why this suddenly doesn't work and how it can be fixed? (Please see images for more info).
 
 
  • CU26071940-1 Profile Picture
    CU26071940-1 2 on at
    Excel date string to integer
    I managed to figure out this issue in the end. It turns out two of the entries in the date checked column had a date and time entry as opposed to just a date (although I'm still not sure how that happened as the whole column is formatted with the same date format, and the dates are selected with the date picker), I just couldn't see it from an initial look at the cell, I had to select the cell and look at the formula bar.
    I found out after looking back at the inputs after a failed run and realising that 2 of the strings being input from the date checked column were decimals and the others were all whole numbers. Once i re entered the dates and the string input as a whole number, the flow worked again.
     
    On a side note, I did try changing the DateTime Format to ISO 8601 when I first set up the flow, but the date stamp that returned was in the wrong format ("2024-12-03T00:00:00.000Z") and at the time I couldn't see how to change it to just stamp the date as dd/mm/yyyy.
  • Suggested answer
    rzaneti Profile Picture
    rzaneti 3,365 on at
    Excel date string to integer
     
    Since you mentioned that the flow was working fine until today, could you please confirm if you have any empty or null entries in your Date Checked column in the Excel column? If you do, you may handle this scenario before using the addDays, since an empty or null value is not convertible to an integer. 
     
    Related to David's idea, if you are trying to simply return the Date Checked from the Excel file, you can simply change the DateTime Format dropdown to ISO 8601 and then all dates will be returned in a timestamp format, without the need of using an addDays, to get the column's actual date. 
     
    I'm also sharing this article about date/time expressions in Power Automate, which can be useful for you or for any other user that finds this thread in the future: https://digitalmill.net/2024/04/12/handling-date-and-time-in-power-automate/
     
    Let me know if it works for you or if you need any additional help!

    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/
  • David_MA Profile Picture
    David_MA 8,929 on at
    Excel date string to integer
    What date format do you have selected in the Excel action? If you do not have an option selected, try choosing the Serial Number option:

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,445

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,741

Leaderboard