Error with Excel Date and Planner

(1) Share

Report

Posted on by 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: Power Automate Screenshot.png

Categories:

Building flows

I have the same question (0)

All responses (21)

Answers (1)

RobElliott 10,323 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
MilesBBB 31 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
ChristianAbata 8,951 Most Valuable Professional on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
MilesBBB 31 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ChristianAbata 8,951 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
MilesBBB 31 on at

Like (0)

Report

@yashag2255 @RobElliott
Thanks again for your guys help on this.
Here are the screenshots on my current issues. Thanks!

Was this reply helpful? Yes No
ChristianAbata 8,951 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
MilesBBB 31 on at

Like (0)

Report

@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?

Was this reply helpful? Yes No