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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,159)