How Do you add A Due Date to a Planner To-Do from an Excel Spreadsheet

[agoldstein]

I am trying to add a start date to a planner to-do. Currently, the date is in an Excel spreadsheet and is formatted as mm/dd/yyyy

When I run the flow, it gives me the Error Message: The request is invalid: Cannot convert the literal '43891' to the expected type 'Edm.DateTimeOffset'.

How do I fix that?

[efialttes]

Hi!
Excel internally stores dates as integers.
Conversion steps here
https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-dates-are-converted-to-integers-in-S...

You can also select the whole column in Excel and change its data type to text format.

Hope this helps

[agoldstein]

Ok, that semi-works. 

I added this: 

formatDateTime(addDays('1900-01-01',add(int(outputs('Compose')),-2)),'yyyy-MM-dd')

into the workflow as a compose and it created a date (1899-12-31). It should have been 2020-03-31

Am I supposed to change a part of the formula?

[fchopo]

Hello @agoldstein 

Have a look at this thread: https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/td-p/134200

According to it, your formula should be:

addDays('1900-01-01T00:00:00Z',int(outputs('Compose')))

Hope it helps!

Ferran 

[efialttes]

@agoldstein 

In order to make this formulas work both @fchopo and me are assuming the Excel date is stored in a Compose action block called 'Compose', so its outputs will look like

outputs('Compose')

I would say @fchopo  approach is more appropriate than my first suggestion, using formatdatetime(..., 'yyyy-MM-dd') to provision a date in Planner will probably fail due to date format incompatibility

But, please also note if you take 1900-01-01 as the reference date, you will nedd to substract 2 days. So, once you store your Excel date in a Compose action block called 'Compose', I would try with:

addDays('1899-12-30T00:00:00Z',int(outputs('Compose')))

The reason for this 2 days gap (1900-01-01 vs 1899-12-30) is quite tricky, explained here:

Days Since 1900-01-01
There were 43938 days since January 1, 1900.

This number (+2) is used in the Microsoft Excel date functions such as DAY(serial_number) and WEEKDAY().

Note, Excel's serial number is 2 higher than the number on this page.

    In Excel January 1 is serial number 1. In this converter midnight January 1, 1900 is 0, after 1 day it is midnight on January 2. To correct this you have to add/subtract 1.
    Excel incorrectly sees 1900 as a leap year (for compatibility with Lotus 1-2-3) so you have to add/subtract 1 to all days when using in Excel. Also see: Excel incorrectly assumes that the year 1900 is a leap year

Thanx!

[agoldstein]

Ok so here is my flow so far:

I am using the below formula for Compose 2:

addDays('1899-12-30T00:00:00Z',int(outputs('Date')))

But it still isn't working. Do I need to do anything with the Date Compose first?

[fchopo]

Hello @agoldstein 

Is there an error converting the value? Or is the flow creating the planner task with an incorrect date?  Could you share a screenshot of your execution in order to see the values after each action?

Thanks

[efialttes]

@agoldstein 

Check this topic.

https://powerusers.microsoft.com/t5/Building-Flows/Help-Required-Setting-Outlook-365-Event-End-Time-...

Marked as solved today, and please note format accepted by Planner is ISO 8601, for example
2018-04-15T13:00:00.0000000Z

I believe the expression @fchopo suggests provides ISO 8601 format also. So if not working yet, it's weird... we must be missing something really simple here, but we cannot see it

Thanx!

[agoldstein]

Below is the screenshot of the test file:

[agoldstein]

So I got it to work. IDK what I did but removing the date output and then adding it back in fixed the issue.

Below is the new flow:

With compose 2 being:

addDays('1899-12-30T00:00:00Z',int(outputs('Compose')))