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!
Solved! Go to Solution.
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
Since Excel calculates dates as an integer of days since 1/1/1900 this was my work around for that:
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
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.
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!
Since Excel calculates dates as an integer of days since 1/1/1900 this was my work around for that:
I was looking for a solution to this "Excel date to Flow date" issue, thank you for the addDays() function.
@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.
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).
Thank you for your quick response!
I solved it!
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?
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 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.
Hi! How did you change your connection to regular excel instead of excel on line?
@bloomwoz 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]
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.
Anyway, on to the fix. Here is my ugly way for solving this:
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:
User | Count |
---|---|
102 | |
37 | |
30 | |
24 | |
16 |
User | Count |
---|---|
132 | |
53 | |
53 | |
37 | |
26 |