cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RiKx
Frequent Visitor

Import date & dime to planner from Excel

Can anyone help, I've tried various tactics to get my excel table to create planner tasks with a date & time but i seem to always be getting a variation of the error  below in bold. 

 

The 'inputs.parameters' of workflow operation 'Create_a_task' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'body/startDateTime' is required to be of type 'String/date-time'. The runtime value '"if(equals(items('Apply_to_each')?['07/06/202110:00:00'],''),null,addDays('12/30/1899',int(items('Apply_to_each')?['07/06/202110:00:00']),'yyyy-MM-ddTHH:mm:ss'))"' to be converted doesn't have the expected format 'String/date-time'.

 

i have a due date & start time column and and end time column (due date is same day) but I cant get flow/PA passed this error whether i use text or formatted columns in excel to create the datetime for the create a task planner connector??

1 ACCEPTED SOLUTION

Accepted Solutions
v-duann-msft
Community Support
Community Support

Hi @RiKx 

 

Thank you for posting.

 

According to your description, you would like to create tasks based on excel file which contains date and time. If any misunderstanding, please kindly let me know.

 

I created the excel as you required:

v-duann-msft_0-1621396918013.png

 

As you known, the date comes from excel is not formatted as 'yyyy-MM-dd' but an integer or float if you also set up time. So the best way to convert them to date is get <start date&time> and <end date&time> as float first, and then convert them to date format step by step.

 

For date: formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['Date])),'yyyy-MM-dd')

For date&time: addSeconds('1899-12-30',int(formatNumber(mul(float(items('Apply_to_each')?['due date & start time']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

 

Here is my flow for your reference(You can reduce compose actions if you combine expression):

v-duann-msft_1-1621396918019.png

 

Flow in detail:

int(first(split(items('Apply_to_each')?['due date & start time'],'.')))

addSeconds('1899-12-30',int(formatNumber(mul(float(items('Apply_to_each')?['due date & start time']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

v-duann-msft_2-1621396918023.png

 

float(items('Apply_to_each')?['end time'])

add(outputs('Compose_3'),outputs('Compose'))

v-duann-msft_3-1621396918027.png

 

addSeconds('1899-12-30',int(formatNumber(mul(outputs('Compose_4'),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

v-duann-msft_4-1621396918030.png

 

After testing, I’m able to create task based on date from excel.

v-duann-msft_6-1621397076558.png

 

You can also take a look on below video.

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

 

Hope the content above may help you.

 

Best regards,

Anna

View solution in original post

1 REPLY 1
v-duann-msft
Community Support
Community Support

Hi @RiKx 

 

Thank you for posting.

 

According to your description, you would like to create tasks based on excel file which contains date and time. If any misunderstanding, please kindly let me know.

 

I created the excel as you required:

v-duann-msft_0-1621396918013.png

 

As you known, the date comes from excel is not formatted as 'yyyy-MM-dd' but an integer or float if you also set up time. So the best way to convert them to date is get <start date&time> and <end date&time> as float first, and then convert them to date format step by step.

 

For date: formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['Date])),'yyyy-MM-dd')

For date&time: addSeconds('1899-12-30',int(formatNumber(mul(float(items('Apply_to_each')?['due date & start time']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

 

Here is my flow for your reference(You can reduce compose actions if you combine expression):

v-duann-msft_1-1621396918019.png

 

Flow in detail:

int(first(split(items('Apply_to_each')?['due date & start time'],'.')))

addSeconds('1899-12-30',int(formatNumber(mul(float(items('Apply_to_each')?['due date & start time']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

v-duann-msft_2-1621396918023.png

 

float(items('Apply_to_each')?['end time'])

add(outputs('Compose_3'),outputs('Compose'))

v-duann-msft_3-1621396918027.png

 

addSeconds('1899-12-30',int(formatNumber(mul(outputs('Compose_4'),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')

v-duann-msft_4-1621396918030.png

 

After testing, I’m able to create task based on date from excel.

v-duann-msft_6-1621397076558.png

 

You can also take a look on below video.

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

 

Hope the content above may help you.

 

Best regards,

Anna

View solution in original post

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Kudoed Authors
Users online (3,144)