Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

Need Help Converting Excel Formula Dates (Float Values) to Actual Dates in Power Automate

(0) ShareShare
ReportReport
Posted on by 15

Hello everyone,

I’m building a flow in Power Automate that references an Excel table stored on SharePoint. The spreadsheet has columns containing formulas for employee probation periods (e.g., 420h and 840h). These formulas can return a numeric “date serial” (like 45569) or sometimes an empty cell/text if the calculation doesn’t apply. I need to:

  1. Compare each row’s “date” to today’s date (to see if the period ends today).
  2. Send an email alert if it matches today.
  3. Update the same row with a timestamp once the email is sent.
 

However, I keep running into template expression errors when trying to use int(), and so on, especially if the cell is empty, has text, or if it’s a float instead of an integer. For example:

  • Error: Unable to process template language expressions in action 'Set_variable' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.
 
The expression used in Set variable : 
 
if(
    and(
        not(empty(items('Apply_to_each')?['EVAL_x002e_840H'])),
        not(equals(items('Apply_to_each')?['EVAL_x002e_840H'], '1900-01-00')),
        not(equals(items('Apply_to_each')?['EVAL_x002e_840H'], null))
    ),
    formatDateTime(
        addDays('1899-12-30', int(string(items('Apply_to_each')?['EVAL_x002e_840H']))),
        'yyyy-MM-dd'
    ),
    null
)
 
Despite these attempts, some rows still cause the flow to fail if they contain non-numeric data or if the date serial has decimals. I’m also aware that Power Automate doesn’t directly support a floor() function.
 
 

My questions for the community:

What’s the best practice for handling date serials (especially with fractional parts) in Excel-based flows?

 

Any guidance or recommended patterns would be greatly appreciated. Thanks in advance for your help!
 
  • 3xceln4ute Profile Picture
    3xceln4ute 15 on at
    Need Help Converting Excel Formula Dates (Float Values) to Actual Dates in Power Automate
    Hi,
     
    Thnak you for your replies. However, the issue is stille ongoing.
     
    The expression used in "Set variable":
    if(
      and(
        not(empty(items('Apply_to_each')?['EVAL_x002e_840H'])),
        or(
          isFloat(items('Apply_to_each')?['EVAL_x002e_840H']),
          isInt(items('Apply_to_each')?['EVAL_x002e_840H'])
        )
      ),
      formatDateTime(
        addDays('1899-12-30', int(triggerBody()?['EVAL_x002e_840H'])),
        'yyyy-MM-dd'
      ),
      null
    )
    The expression used in "Condition 840h" connector :
    equals(variables('DateConvertie 840h'), variables('DateAujourdhui'), utcNow('yyyy-MM-dd'))
     
     
    This situation is driving me crazy, as it seems that there is no solution...
  • Suggested answer
    David_MA Profile Picture
    David_MA 8,884 on at
    Need Help Converting Excel Formula Dates (Float Values) to Actual Dates in Power Automate
    If I understand correctly, you want to know how to convert an Excel serial date to a regular date? If those, use this expression: addDays('1899-12-30',int(triggerBody()['number_1']),'dd-MM-yyyy')

    Replace triggerBody()['number_1'] with the value from Excel.
  • Suggested answer
    SwatiSTW Profile Picture
    SwatiSTW 301 on at
    Need Help Converting Excel Formula Dates (Float Values) to Actual Dates in Power Automate

    Here is how you can fix the issue step by step

    1. Start by initializing two variables: one for today's date (use utcNow() as the value) and another for the processed date (leave it empty for now).

    2. Use the "List rows present in a table" action to get data from your Excel file stored in SharePoint or OneDrive.

    3. Add an "Apply to each" loop to go through each row in the Excel table.

    4. Inside the loop, add a "Compose" action to check the value of the column with the Excel date serial (like EVAL_x002e_840H). This is just for debugging.

    5. Add a "Set variable" action to calculate the real date. Use this expression to convert the Excel date serial into a proper date

    if(
    and(
    not(empty(items('Apply_to_each')?['EVAL_x002e_840H'])),
    or(
    isFloat(items('Apply_to_each')?['EVAL_x002e_840H']),
    isInt(items('Apply_to_each')?['EVAL_x002e_840H'])
    )
    ),
    formatDateTime(
    addDays('1899-12-30', int(items('Apply_to_each')?['EVAL_x002e_840H'])),
    'yyyy-MM-dd'
    ),
    null
    )
    1. Add a "Condition" action to check if the processed date (from the variable) matches today's date (from the variable you initialized earlier). Use this expression
    equals(variables('ProcessedDate'), variables('DateAujourd\'hui'))
    1. If the condition is true, add an action to send an email. This will notify the person about the probation period.

    2. Also, if the condition is true, update the same Excel row to add a timestamp or mark the row as processed.

    3. If there are any invalid or empty rows, the flow will skip those rows automatically because of the expression in step 5.

    4. Test the flow and adjust as needed. Use "Compose" actions to see the values at each step if something doesn’t work.

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,343

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,703

Leaderboard