cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sherif77
Helper II
Helper II

importing dates from excel to sharepoint list

Hello,

 

can anyone help me to transfer the dates in my excel to SharePoint , I managed already the process of transferring all other data / columns in my excel file except dates 

 

what expression I should be using in the submission date field

 

i used in expiry date ( compose connector ) the following 

addDays('1899-12-31',int(item()['Expiry Date']),'YYYY-MM-DD')   each time i run my flow i receive comment about format date "string/date"  . any suggestion
 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @sherif77 

The syntax and function for addDays('1899-12-31',int(item()['Expiry Date']),'YYYY-MM-DD') seem off.

How many days are you trying to add (or subtract) from the date "1899-12-31"?

The functions addDays() takes the # of days as an  argument. Your function may not work for a few reasons:

1) the int function will not convert a date with format 'YYYY-MM-DD' to an integer since 'YYYY-MM-DD' is not a valid non-integer number.

2) the argument int(item()['Expiry Date']),'YYYY-MM-DD' is not a valid number (+ or -) therefore the addDays function will not work.

3) Not sure if this is on purpose but 'YYYY-MM-DD' will not return a valid date format. The correct syntax is 'yyyy-MM-dd'

4) You need a formatDateTime in your function if you explicitly want to set a date format.

Some tips.

To explicitly format your expiry date then use the syntax below

dayOfMonth(formatDateTime(utcNow(),'yyyy-MM-dd')) = 19th day of the month

If you want to get a specific number of days forward or backward from '1899-12-31' then use this syntax. Use the dayOfMonth function to get a valid day.

addDays('1899-12-31',dayOfMonth(formatDateTime(utcNow(),'yyyy-MM-dd')) = 1900-01-19T00:00:00.0000000

 

Cheers,

Aman

------------------------------------------------------------------------------------------------------
If my post helps you with your problem or answers your question, please mark it Solved or Answered. This helps anyone with similar challenges. If you like my response, please give it a Thumbs Up.
------------------------------------------------------------------------------------------------------

View solution in original post

2 REPLIES 2
v-litu-msft
Community Support
Community Support

Hi @sherif77,

 

The expression addDays('1899-12-31', int(item()['Expiry Date']),'YYYY-MM-DD') couldn't work, I guess it is caused by the second parameter, the second parameter should be a valid integer. More details, you could refer to: addDays() function .

 

If you could share the output of "item()['Expiry Date']", error details, and screenshots of the previous action, they could help us assist you better.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @sherif77 

The syntax and function for addDays('1899-12-31',int(item()['Expiry Date']),'YYYY-MM-DD') seem off.

How many days are you trying to add (or subtract) from the date "1899-12-31"?

The functions addDays() takes the # of days as an  argument. Your function may not work for a few reasons:

1) the int function will not convert a date with format 'YYYY-MM-DD' to an integer since 'YYYY-MM-DD' is not a valid non-integer number.

2) the argument int(item()['Expiry Date']),'YYYY-MM-DD' is not a valid number (+ or -) therefore the addDays function will not work.

3) Not sure if this is on purpose but 'YYYY-MM-DD' will not return a valid date format. The correct syntax is 'yyyy-MM-dd'

4) You need a formatDateTime in your function if you explicitly want to set a date format.

Some tips.

To explicitly format your expiry date then use the syntax below

dayOfMonth(formatDateTime(utcNow(),'yyyy-MM-dd')) = 19th day of the month

If you want to get a specific number of days forward or backward from '1899-12-31' then use this syntax. Use the dayOfMonth function to get a valid day.

addDays('1899-12-31',dayOfMonth(formatDateTime(utcNow(),'yyyy-MM-dd')) = 1900-01-19T00:00:00.0000000

 

Cheers,

Aman

------------------------------------------------------------------------------------------------------
If my post helps you with your problem or answers your question, please mark it Solved or Answered. This helps anyone with similar challenges. If you like my response, please give it a Thumbs Up.
------------------------------------------------------------------------------------------------------

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,284)