cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mattw112IG
Impactful Individual
Impactful Individual

Working with Dates from Excel OneDrive

I have excel sheet on OneDrive for business.

In the sheet it has a table with data

One of the columns is a date column that is formatted as short date.

I also have a SharePoint list that has columns including one formatted as date (not time)

 

In my flow I want to read values from Excel and populate SharePoint List.

 

But I keep getting errors about this date.  It looks like the date is coming through like '42092.7083333333' for example.

 

So I have tried doing a formatdatetime() with multiple formatting options and always gives error.

 

Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '17608': 'In function 'formatDateTime', the value provided for date time string '42092.7083333333' was not valid. The datetime string must match ISO 8601 format.'.

 

 

My latest expression is like this:

formatDateTime(items('Apply_to_each')?['engagement_start_date'], 'dd/MM/yyyyTHH:mm:ss')
 
What can I do to convert this number date into a form that flow/SP will accept?
 
Thanks,
Terry

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-duann-msft
Community Support
Community Support

Hi @Mattw112IG 

 

Thank you for posting.

 

According to your description, you would like to copy data which is formatted in date from excel to SharePoint. If any misunderstanding, please kindly let me know.

 

You are right, we need to do date conversion for date stored in excel when we transfer to SharePoint. If you get result like 42092.7083333333, it means you should have configured actual date and time in excel. Because the integer part means date and the float-number part means time.

 

As you mentioned, you don’t need to include time in sharepoint. Thus, we just need to convert integer part to actual date. So the fload-number part is uncessary to retain, we need to use split function to remove it.

 

Flow overview:

v-duann-msft_0-1616658025358.png

 

Expression:

first(split(string(variables('VarDate')),'.'))

formatDateTime(addDays('1899-12-31',int(outputs('Compose'))),'yyyy-MM-dd')

 

Once tested, I’m able to convert 42092.7083333333 to 2015-03-30.

v-duann-msft_1-1616658025363.png

 

Here are multiple videos and threads showing us how to convert value to date format from excel. Please feel free to check. If you still have any problem, please share with me.

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

https://powerusers.microsoft.com/t5/Building-Flows/Convert-Float-to-Integer-Ms-Flow-in-addDays-Expre...

 

Best regards,

Anna

View solution in original post

2 REPLIES 2
annajhaveri
Community Champion
Community Champion

@Mattw112IG  do you have date stored in excel or is it date and time? Excel stores date as numbers that is why you are getting  a number when you fetch date column value.

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.
v-duann-msft
Community Support
Community Support

Hi @Mattw112IG 

 

Thank you for posting.

 

According to your description, you would like to copy data which is formatted in date from excel to SharePoint. If any misunderstanding, please kindly let me know.

 

You are right, we need to do date conversion for date stored in excel when we transfer to SharePoint. If you get result like 42092.7083333333, it means you should have configured actual date and time in excel. Because the integer part means date and the float-number part means time.

 

As you mentioned, you don’t need to include time in sharepoint. Thus, we just need to convert integer part to actual date. So the fload-number part is uncessary to retain, we need to use split function to remove it.

 

Flow overview:

v-duann-msft_0-1616658025358.png

 

Expression:

first(split(string(variables('VarDate')),'.'))

formatDateTime(addDays('1899-12-31',int(outputs('Compose'))),'yyyy-MM-dd')

 

Once tested, I’m able to convert 42092.7083333333 to 2015-03-30.

v-duann-msft_1-1616658025363.png

 

Here are multiple videos and threads showing us how to convert value to date format from excel. Please feel free to check. If you still have any problem, please share with me.

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

https://powerusers.microsoft.com/t5/Building-Flows/Convert-Float-to-Integer-Ms-Flow-in-addDays-Expre...

 

Best regards,

Anna

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (6,085)