cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Excel dates turned into integers?

Hello! I'm a fairly new flow user here and I have a flow which needs to extract dates from an excel spreadsheet and email them out. The kicker is that I cannot figure out why flow is importing the data from the spreadsheet as plain numbers instead of as dates even though the cells themselves are formatted as dates for example the date 06/18/12 is being read in as 41078, any advice? thanks in advance for the help!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-yamao-msft
Community Support
Community Support

Hi @Anonymous,

 

I assume that you are using Excel Online. It seems that Excel Online will return the date column as plain numbers instead of date format.

 

I would suggest you use Excel, it works fine and will return date as expected.

 

I will help confirm this issue on my side.

 

 

Best regards,

Mabel Mao

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

View solution in original post

crf200
Advocate IV
Advocate IV

Since Excel calculates dates as an integer of days since 1/1/1900 this was my work around for that:

 

addDays('1900-01-01',int(items('_')?['Date']))

View solution in original post

36 REPLIES 36
v-yamao-msft
Community Support
Community Support

Hi @Anonymous,

 

I assume that you are using Excel Online. It seems that Excel Online will return the date column as plain numbers instead of date format.

 

I would suggest you use Excel, it works fine and will return date as expected.

 

I will help confirm this issue on my side.

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yamao-msft,

 

That is correct, I'm using an Excel Online (for business) connector. I'll give it a try using regular excel and see if that changes anything. Thanks for the input.

Anonymous
Not applicable

Got the connector switched to regular excel and it works like a charm. It's always the simple things eh? Thanks again for the help!

crf200
Advocate IV
Advocate IV

Since Excel calculates dates as an integer of days since 1/1/1900 this was my work around for that:

 

addDays('1900-01-01',int(items('_')?['Date']))

I was looking for a solution to this "Excel date to Flow date" issue, thank you for the addDays() function.

Anonymous
Not applicable

 


@crf200 wrote:

Since Excel calculates dates as an integer of days since 1/1/1900 this was my work around for that:

 

addDays('1900-01-01',int(items('_')?['Date']))

Thanks, this was extremely helpful. Though for some reason this date was adding two more days to the expected result so I had to change it to 1899-12-30. 

Anonymous
Not applicable

I'm trying to import an Excel-Table to my SharePoint-List. My table includes a Date-Column.

Flow can't import this column into my SharePoint-List - as you said, it's calculated as an integer.

But I don't understand how to combine your formular with my column "date".

In my german Flow it's named "Dynamischer Ausdruck", I don't know if it is called "Dynamic term" in english.

 

I tried several things like "addDays('1900-01-01',int(items('DeliveryDate')?['Date']))" but no matter what I try, Flow always tells me, that it is not right. 

Could you please help me?

Thank you very much!

I recently corrected an issue I was having in another flow when it came to working with numbers.

 

My questions is on your Delivery Date field in Excel. Is it a combination Date/Time or simply the Date Field?

 

To convert a Date/Time you should use a 'float' function instead of the 'int' function. Integers are whole numbers with no decimals but the float will return the number string with decimals.

 

addDays('1899-12-30',float(items('DeliveryDate')?['Date']))

 

I also found that the formula was somehow calcuating an extra 2 days, to fix this and get the proper date in an addDays I subtracted 2 days from the start date (above).

Anonymous
Not applicable

Thank you for your quick response!

I solved it!

 

Anonymous
Not applicable

Hi, I am running into the same issue and I am not sure how to use excel instead of excel on line. My file is currently on sharepoint and it gets contantly updated by my team so I can not have a hard copy saved. Please help .Thank you 

addDays() only takes integers now, has this changed? any alternative?


 https://aka.ms/logicexpressions#adddays

 
Anonymous
Not applicable

Hi

 

Sorry I am really new to Power Automate..

 

I am having the Excel date issue, I can't find the Excel instead of Excel online option for a connector.

 

Where should I put this line of code please?

 

Thank you 

 

Lisa 

Anonymous
Not applicable

@Anonymous  Hi ~ try this Compose expression:  

if(empty(items('ApplyEach_Dates')?['YourDateField']),'',addDays('1899-12-30',int(items('ApplyEach_Dates')?['YourDateField']),'MM/dd/yyyy'))


In my example, I'm trying to convert two date columns, named "Work Date" and "Week-Ending". The ApplyEach_Dates is just an Apply to Each control that I renamed (awkwardly). You need a compose action for each date. Then I plug the output of the compose action into the SharePoint column.

Excel dates if empty.PNG

Anonymous
Not applicable

Hi! How did you change your connection to regular excel instead of excel on line?

Anonymous
Not applicable

@Anonymous  I didn't change the connection. I didn't have any issue with that. It's Excel Online (Business) and my Excel files are stored in SharePoint Online. I did  have to do these shenanigans to get the Excel action to cope with dynamic file names (i.e., not knowing the file name in advance). Another member of this board provided a great solution involving a Split expression on the Thumbnail Large property of the Excel file: split(split(variables('varThumb'),'items%2f')[1],'%3fversion%3d')[0]

 

excel import2.png

 

Not sure what you're trying to do, but if you don't want to read all rows in the Excel file, you could try launching a flow manually on a particular row from within Excel: https://www.flow.microsoft.com/en-us/blog/introducing-microsoft-flow-integration-in-excel/

 

 

 

 

I happened across this thread when I ran into the same issue a lot of people are facing here. Getting a time from Excel Online returns the value as a number rather than date time.

 

As was mentioned above there used to be a good way to handle this by using the AddDays method. Problem with that is, now it doesn't appear to take Float values (those with decimal places) but only takes Integers. As a result, we can see here some suggestions to just grab the date, this prevents us from logging the exact time though.

 

In addition, I've seen some comments that the date is 2 days off when coming from Excel. Fortunately I came across this post:
https://powerusers.microsoft.com/t5/Using-Flows/String-was-not-recognized-as-a-valid-DateTime/td-p/4...
Which references this site: https://www.epochconverter.com/seconds-days-since-y0

 

The explanation for the 2 additional days is explained there. Copying it here in case its ever taken down:

 

Note, Excel's serial number is 2 (days) higher than the number on this page.

  • In Excel January 1 is serial number 1. In this converter midnight January 1, 1900 is 0, after 1 day it is midnight on January 2. To correct this you have to add/subtract 1.
  • Excel incorrectly sees 1900 as a leap year (for compatibility with Lotus 1-2-3) so you have to add/subtract 1 to all days when using in Excel. Also see: Excel incorrectly assumes that the year 1900 is a leap year

 

Anyway, on to the fix. Here is my ugly way for solving this:

 

split(string(mul(mul(float(items('DeliveryDate')?['Date']), 24), 60)),'.')[0]
 
Add that to an "Add to Time" and set the Time unit to Minute and you should be good:
 
addToTime.png
 
Yep, that's ugly. Quick break down of what's happening above:
 
Converting the value from a string to a float (needed for next mul function): float(items('DeliveryDate')?['Date'])
Multiplying that value by 24 to get hours: mul(float(items('DeliveryDate')?['Date']), 24)
Multiplying that value by 60 to get minutes: mul(mul(float(items('DeliveryDate')?['Date']), 24), 60)
Converting the value back to a string (need for Split): string(mul(mul(float(items('DeliveryDate')?['Date']), 24), 60))
Splitting the value by the decimal: split(string(mul(mul(float(items('DeliveryDate')?['Date']), 24), 60)),'.')
Getting the first value after the split: split(string(mul(mul(float(items('DeliveryDate')?['Date']), 24), 60)),'.')[0]
 
I tried to go down to seconds but the "Add to Time" step didn't like how large the value was. It appears it can only handle up to 
999,999,999. With the above logic you can go as high as 3801-04-29 10:39:00. Of course if you split it up and have multiple Add to Time steps you could go higher.
 
And adding this here as it helped me understand the functions available: https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

Hi @JaredRic 

 

I'm attempting your solution to this and have hit a wall that I hope you can help with.. 

 

Where do I put the Add to time action? I've tried it inside the apply to each and before it and both give me errors. I have one date/time that I want to input (which originates from some Form results) called 'Completion time'. I've attempted using your expression: 

split(string(mul(mul(float(items('Completion time')?['Date']), 24), 60)),'.')[0]
 
I get this error message:
The template validation failed: 'The repetition action(s) 'Completion time' referenced by 'inputs' in action 'Add_to_time' are not defined in the template.'.
 
I've moved the date to be British format, not sure if that matters?
 
error.PNG
 
Thanks!
 

 

Helpful resources

Announcements

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (4,215)