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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,797)