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.