web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Error with Excel Date ...
Power Automate
Unanswered

Error with Excel Date and Planner

(1) ShareShare
ReportReport
Posted on by 31

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:
I have the same question (0)
  • RobElliott Profile Picture
    10,323 Super User 2025 Season 2 on at

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

    excelDateFormattoPlanner.png

    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 Profile Picture
    31 on at

    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 Profile Picture
    8,951 Most Valuable Professional on at

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

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

  • MilesBBB Profile Picture
    31 on at

    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 Profile Picture
    8,951 Most Valuable Professional on at

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

  • yashag2255 Profile Picture
    24,769 Super User 2024 Season 1 on at

    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 Profile Picture
    31 on at

    @yashag2255  @RobElliott 

    Thanks again for your guys help on this.

    Here are the screenshots on my current issues.  Thanks!

    SS2.pngSS3.pngSS4.png

  • ChristianAbata Profile Picture
    8,951 Most Valuable Professional on at

    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 Profile Picture
    10,323 Super User 2025 Season 2 on at

    @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 Profile Picture
    31 on at

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 522 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 364 Moderator

#3
abm abm Profile Picture

abm abm 243 Most Valuable Professional

Last 30 days Overall leaderboard