Excel lists due dates, Power Automate sends reminder email, due dates not converting properly.

[josva]

I have created an Excel workbook to keep track of upcoming expiration dates. There are 18 columns of information, but for simplicity's sake, I will reduce it to the most pertinent information. Each row lists a separate piece of data, an expiration date, and two calculated date fields. The two calculated date fields are based on the expiration date to convert the expiration date to a workday. The difference between the two calculated date fields is that the second one takes the expiration date and subtracts a month to create a "reminder" date. I will call the two calculated date fields "Calculated Expiration Date" and "Reminder Date".

From here, things diverge into two power automate flows that in essence do the same thing. I have a power automate flow that retrieves the list of information, filters by the Calculated Expiration Date, and then emails out the list of items that are expiring (the second one does the same thing, but uses the Reminder Date to send out an email a month before the Calculated Expiration Date).

The challenge I face currently is this:

I am using this exact flow to create a reminder email for another list that works as intended, so I'm puzzled as to why it won't work for this list. The differences between the workbooks are that I did not create the one I'm working on now, and it's saved on Sharepoint instead of my Onedrive. Neither of those seem likely to cause this problem. I have copied the formatting exactly from my other workbook, so the date should be working exactly the same.

I have tried to use the solution presented here, but it gives me the same result. I used the solution to produce this:

addseconds('1899-12-30',int(formatnumber(mul(float(triggerBody()['Reminder Date']),86400),'0')))

What have I done wrong?

[wskinnermctc]

In the action "List Rows Present in a Table" there is an option for DateTime format. 

Change the Date Time Format to ISO 8601.

The number 45321 is the Serial date from Excel. Power Automate can read Excel data as Serial or ISO 8601.

If the settings is ISO 8601:

equals(formatDateTime(item()?['Reminder Date'],'yyyy-MM-dd'),utcNow('yyyy-MM-dd'))

If the settings is in Serial:

equals(formatDateTime(addseconds('1899-12-30',int(formatnumber(mul(float(triggerBody()['Reminder Date']),86400),'0'))),'yyyy-MM-dd'),utcNow('yyyy-MM-dd'))

See if one of those helps you out

[josva]

Thanks for the suggestion. I tried both of those out to no avail. What's strange is that some of the dates are coming through properly. Check out this screenshot.

The expiration date is coming through as a date, but the two calculated fields are not. I've converted them both in excel to serial numbers and then back to dates to make sure they are formatted properly and that they don't have any extra numbers that would indicate time. I've copied the formatting from the "Expiration Date" column to the other two to make sure they are consistent.

I can't think of what else to try other than making a whole new workbook and seeing if that does something.

[wskinnermctc]

The calculated dates are probably going to keep coming in as Serial if power automate doesn't recognize them as dates. So you will have to use the Serial type expression with the calculated fields. For the Expiration date, you will have to use the ISO 8601 expression.

Creating a new workbook is not going to change anything. It has to do with the values in the fields and how Power Automate reads them.

The error you show in your screenshot doesn't have to do with the workbook or values, it looks like it is entered incorrectly. (I'll take some blame for this, but really I just copied what you initially put assuming it was correct.) 

triggerBody()['Reminder Date'] is going to be incorrect.

"triggerBody" is referencing the body of the trigger, which is Recurrence. So this is not correct, you are not getting any values from the Recurrence trigger.

The error message says "cannot be evaluated because property "Reminder Date" cannot be selected". Which makes sense because there is no Reminder Date in the trigger.

So for the Expiration Date which is in ISO 8601 you could use the expression:

equals(formatDateTime(item()?['Expiration Date'],'yyyy-MM-dd'),utcNow('yyyy-MM-dd'))

For the Reminder Date that is in Serial format you could use the expression:

equals(formatDateTime(addseconds('1899-12-30',int(formatnumber(mul(float(item()?['Reminder Date']),86400),'0'))),'yyyy-MM-dd'),utcNow('yyyy-MM-dd'))

Also note that if you have any blank rows in your dates, this will cause an error and will most likely change the date type to Serial. See my post about when Excel date is blank.

[josva]

Thanks @wskinnermctc for the follow up.

What's throwing me off is that this flow worked on another email reminder flow I set up a year ago.

To try and isolate where the problem seems to be occurring I did a few additional tests:

I duplicated the workbook and flow for my other flow that is working (the one from a year ago) and then copied the data from the workbook with expiration dates into my duplicated workbook from a year ago, and the duplicated flow ran without errors.

I moved that duplicated workbook into SharePoint just to see if that did anything, repointed the flow to the new position, and it also ran just fine.

Just to see what would happen, I copied the sheet from the duplicated workbook into the workbook with expiration dates, repointed the flow and ran it again and it didn't work. The problem seems to be centered around the workbook with expiration dates.

I agree that there could be something happening on blank rows. I tried a few times to get your formulas to work, then tried adding a filter array step to filter out blank rows and couldn't figure it out. I was able to work around the blank rows by changing my IFERROR formula in the two calculated date fields to throw a "1" instead of "". It gives me "1/1/1900", but that won't interfere with anything, plus I can hide those columns so they aren't distracting.

I still don't get what the problem is, but I'm going to go with this version that is working.

[wskinnermctc]

I think the issue is trying to deal with calculated columns. Trying to leverage Excel functions like formulas/lookups, as a tool for Power Automate is usually a problem. You should get data into Power Automate, and then have PA perform the functions.

Whatever date checking or comparison you need to do about dates, do it in Power Automate. Use the Expiration Date and perform calculations in PA to get the results.

You could have Power Automate do the date calculation and condition when to send or not send emails. (Resource heavy since it has to perform the calculation on each Expiration Date every scheduled flow.)

or

You could have Power Automate do the date calculation and condition; then write back to the Excel workbook so that you only have dates in the "Calculated Expiration Date" and the "Reminder Date" columns as a reference for a Scheduled flow. (May require separate flows, or an additional condition check to know which dates to pull and perform calculations.)

I don't know when/how this workbook is updated. But I would find a method to only have dates as a reference instead of calculated formulas.

Another alternative is using an Excel Script. I do not have any experience with this, but it seems to be new and people have had success. You make the Script in the workbook, then use a PA flow to run the script and use the results.

[wskinnermctc]

If I was in your position, I would adjust the workbook/table so that a flow could add/calculate the dates needed, and also read the dates to send reminder emails.

Example:

Imagine your workbook had a table like this below. 

Name	ProjectID	Expiration Date	Calculated Expiration Date	Reminder Date	Flow Status
Steve	A123	11/25/2023	11/24/2023	11/1/2023	Completed
Ashley	B321	2/17/2024	2/16/2024	2/1/2024	Reminder
Jack	C987	3/23/2024			Calculate
Susan	D789	4/27/2024			Calculate

I would make a scheduled flow that used the "Flow Status" column as a reference.

• Completed - this row needs nothing and no emails or anything. Basically do nothing.
• Reminder - this row needs an email sent on the reminder date
• Calculate - this row needs the "Calculated Expiration Date" and "Reminder Date" filled in

The flow would run every day and use the Status column as a reference for what actions to perform:

• Trigger - schedule run daily
• List Rows 1 - filter by rows that have Flow Status of "Calculate"
  • For each of these rows calculate the correct "Calculate Expiration Date" and "Reminder Date"
  • For each of these rows update the two date columns and set Flow Status column as "Reminder"
• Delay - 10 minutes to allow the workbook to complete and save
• List Rows 2 - filter by rows that have Flow Status of "Reminder"
• Filter Array - filter the List Rows 2 by Reminder Date is equal today
  • For Each row in Filter Array - send a reminder email
  • For Each row in Filter Array - Update row Flow Status to "Completed"

This is not a precise example, but it is a general idea of the approach I would use so it would be more solid and not depend on an Excel calculation.