Error with Excel Date and Planner

[MilesBBB]

Hi,

I'm having this issue with getting the date format to run from Excel and into a Planner task I want to create.

The dates in excel are formulas which I need to keep. 

The error message I keep getting is "The request is invalid:
Cannot convert the literal '43955' to the expected type 'Edm.DateTimeOffset'.

This occurs when I "Create a Task"and set the start date to a date in my excel table.  I again have the same problem with my due dates.  

Here how I've set it up so far.  The function/expressions I need are for the Start Date and Due Date, any help would be greatly appreciated thank you:

[RobElliott]

@MilesBBB you can't create the start and due dates for a Planner task directly from your date columns in Excel. This is because Excel stores a date as an integer even though it displays it as a date.  So you need to convert it from the integer back to a date. I do this with a couple of Compose controls. In my excel table I have columns called Start and Due and the expressions in the Compose controls are:

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Start']),-2)), 'yyyy-MM-dd')
formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Due']),-2)), 'yyyy-MM-dd')

Then you select the outputs of each expression from the dynamic content and use that to set the date in your task:

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[MilesBBB]

Thanks for you help Rob.  I've done as you've said and received this error:

The template validation failed: 'The inputs of template action 'Compose' at line '1 and column '3170' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Compose' to be referenced by 'repeatItems' or 'items' functions.'.

Here's what I put in the first Compose operation: 

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Start Date']),-2)), 'yyyy-MM-dd')

The only thing I changed was to put "Start Date" rather than "Start" as the header for the column I want to reference is called that.

What have I done wrong?

[ChristianAbata]

hey @MilesBBB  please you can follow my video I show how to manage this convertion.

https://www.youtube.com/watch?v=hjkftEtPPCg

[MilesBBB]

Thanks but that doesn't work for me.  My excel formulas need to remain that way as they will be regularly updated and I can't keep going back to change them into text.  Thanks though.

[ChristianAbata]

@yashag2255 shure you have something with excel dates that I'm shure could help here jejej

[yashag2255]

Hi @MilesBBB 

Thanks for tagging @ChristianAbata, I definitely sense something here as well!

@MilesBBB, I believe the expression shared by @RobElliott  should pretty much do it. The error you have mentioned seems to be due to some naming issue on the apply to each control. If you can share a screenshot of the current flow configuration, we might be able to better identify the issue and assist you with this. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[MilesBBB]

@yashag2255  @RobElliott 

Thanks again for your guys help on this.

Here are the screenshots on my current issues.  Thanks!

[ChristianAbata]

my friend @MilesBBB  the error is because the action List Rows converts your data, so if you don't change your column fotmat allways you are going to be see this 35442 value, sorry I recomend you to use sharepoint list instead.

[RobElliott]

@MilesBBB you are using a compose control (Compose 2) but not using the output of that in the create task action.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[MilesBBB]

@RobElliott  Thanks!  How do I fix that?  I don't really know how to use this stuff very well (clearly).  Could you please tell me how?

[v-litu-msft]

Hi @MilesBBB,

In your 4th replay, I can see you use the compose to process the Start Date, convert it into a timestamp. After you process it, when you create the task in planner, you should put the processed date into the create task action, not the original Start Date. In other words, you should combine it in the same Apply to each action:

 In the same way, the Due Date should do as above.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MilesBBB]

@v-litu-msft  thanks for your input.  So I think I've done what you suggested and added in my "Create a Task" operation into the Compose 2 operation.  I then put the Output function in for the start date.  I then got the following error... What did I do wrong?

Thanks,

[v-litu-msft]

Hi @MilesBBB,

Please use the "yyyy-MM-dd" instead of "dd-MM-yyyy" as time format.

Then add "T00:00:00Z" after the Compose, for example:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RobElliott]

@MilesBBB once you've created your Compose you then select the Outputs of that to use in later actions. The Outputs of my ComposeConvertDue is shown below and is used to set the due date in Planner, and similarly the Outputs of the ComposeConvertStart is used for the Planner start date. I never bother with adding the time, just the date.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[MilesBBB]

@v-litu-msft  IT WORKED!!!!!!!!!!!  You are the smartest person alive!!!  Thank you thank you thank you.  I've tried and failed at this SO many times.  

I've got the start dates in which is awesome.  How do I get the Due Dates in?  Do I create more operations underneath the current one with compose and update task operations to do that?

Thanks again.  You are a life saver!

[RobElliott]

@MilesBBB you do it with an additional Compose and use the output of that as I showed in the image in my previous post.

Rob
Los Gallardos

[v-litu-msft]

Hi @MilesBBB,

I am glad your Flow could work.

Yes, you could add an additional compose action to process the Due Date get from Excel, under the Compose which process Start Date. The same operation.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MilesBBB]

@v-litu-msft @RobElliott 

Ok just tried to run the same operation again for Due Date and got error messages.

I assume it's something to do with the expression and I need to adjust what it says in 'Compose'??

Secondly, I couldn't figure out what to put in for the 'Task ID' in the Update Task operation.  Any suggestions?  Thanks!

[v-litu-msft]

Hi @MilesBBB,

The parameter in the outputs() function should be the name for the action's output that you want, the previous action name is "Compose 3", so, in the outputs() function should be outputs('Compose_3').

The second answer is to put the update a task action into the loop of Create a task, then click the dropdown of Task Id, click the enter custom value, then you could find the Task ID on the right panel.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.