How do i use a date from excel in the Delay Until function?

[sophieclark]

Hi all!

Hoping this might be a simple one but i'm not sure if it's possible. I'm getting data from a row in excel where I have a column with dates in (pic below) - i want to use this date in a 'Delay Until' box so that i can trigger an email on this date, but i don't know how to format the date in the 'Delay Until' block for it to work. Is this possible, and if so, how do i format the date correctly so that it's recognised?

Thanks!

[ScottShearer]

@sophieclark 

You want to approach this differently.

A better way would be to use a recurrence trigger that runs daily.

If that aren't that many items in Excel, just loop through them and check the date.  If the date is today, then send the email.

Note that the Excel data must be in a table and you'll want to set the date/time format in the List rows action to ISO 8601.

Let me know if you need more help or an example.

[sophieclark]

Hi @ScottShearer , thanks for your response!

Ok that sounds like it might be the better option. I already have the data in a table in excel but to see an example of how it should look in the flow would be really helpful if you can?

Thanks again!

[ScottShearer]

@sophieclark 

Working on example now...

[ScottShearer]

@sophieclark 

See below:

[sophieclark]

Thanks @ScottShearer 

Does the recurrence function need to be the very first function in the flow as i'm not seeing it as an option to select? The flow i have is already pretty extensive so this wouldn't be the first step, unless i need to start a new flow for this particular action?

As part of the conditions of triggering the email, i also need to check if another cell in a different column contains either a 'Yes' or is blank. Would i add this as an additional condition beneath the 'formatdate' so that if its equal to todays date AND the other cell is blank/does not contain 'Yes', then trigger the email? Hope that makes sense!

[ScottShearer]

@sophieclark 

I suggest breaking this out into a new Flow using the recurrence trigger.

You can add additional checks in the existing condition by clicking on the "add"

[sophieclark]

@ScottShearer 

I've set up the flow like your example but i'm getting the following error:

'InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '44980' was not valid. The datetime string must match ISO 8601 format.'.

i have the date expression like this: 

formatDateTime(item()['Remedial Action Completion Date (if fail)'], 'yyyy-MM-dd') 

and the UTC expression like you suggested in your example too.

I've also set the date/time format in the list rows function as ISO 8601. Can you see where i'm going wrong? Is the value in the apply to each just supposed to be 'value' or am i supposed to select the specific date value from the excel here?

[ScottShearer]

@sophieclark 

Go to your List Rows action and click on Advanced properties.  Set the Date format to ISO 8601 and try again please.

[sophieclark]

@ScottShearer I already have that in place. I just reconfirmed it and tested again but still getting the same error: 

[ScottShearer]

@sophieclark 

Please post screen shots of the expressions used in your condition.

Thanks

[sophieclark]

Thanks @ScottShearer - see screenshots below:

[ScottShearer]

@sophieclark 

You MUST use a columns internal name in your expressions - not the display name which is the name you see in a list.  For example,  I see that you reference Remedial Action.... as a column name.  Internal names do not have spaces.

An easy way to retrieve a columns internal name is to go to the list settings and then click on a column name.  The internal name will be at the end of the URL after Field=

example:    ?List=%7BB440ADE7-A9DF-4A30-831C-14BEC800BD69%7D&Field=ButtonResponse

[sophieclark]

Thanks @ScottShearer 

Where do i find the list settings to get the URL? Is that in power automate or in excel? I can't see list settings anywhere. 

Thank you

[sophieclark]

@ScottShearer ive just been doing some more research on this as i really need to get it up and running, and i think the advice you gave me regarding column names is relating to sharepoint lists not excel. I have my data being captured in a table within excel. Do you know what the rules are around expressions in this case? i really cant pinpoint why i'm getting the ISO 8601 error