Unanswered

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

(0) Share

Report

Posted on by 3xceln4ute

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:

Compare each row’s “date” to today’s date (to see if the period ends today).
Send an email alert if it matches today.
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!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

SwatiSTW 741 Super User 2025 Season 2 on at

Like (0)

Report
Here is how you can fix the issue step by step

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).

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

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

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.

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 )

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'))

If the condition is true, add an action to send an email. This will notify the person about the probation period.

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

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

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

Was this reply helpful? Yes No
Suggested answer

David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
3xceln4ute 15 on at

Like (0)

Report
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...

Was this reply helpful? Yes No