cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Excel dates turned into integers?

Hi @dngtie,

 

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

30 REPLIES 30
Highlighted
Community Support
Community Support

Re: Excel dates turned into integers?

Hi @dngtie,

 

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

Highlighted
Frequent Visitor

Re: Excel dates turned into integers?

@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.

Highlighted
Frequent Visitor

Re: Excel dates turned into integers?

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!

Highlighted
Advocate III
Advocate III

Re: Excel dates turned into integers?

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']))
Highlighted
Advocate I
Advocate I

Re: Excel dates turned into integers?

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

Highlighted
Advocate III
Advocate III

Re: Excel dates turned into integers?

 


@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. 

Highlighted
Frequent Visitor

Re: Excel dates turned into integers?

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!

Highlighted
Advocate III
Advocate III

Re: Excel dates turned into integers?

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).

Highlighted
Frequent Visitor

Re: Excel dates turned into integers?

Thank you for your quick response!

I solved it!

 

Highlighted
Regular Visitor

Re: Excel dates turned into integers?

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 

Highlighted
Helper V
Helper V

Re: Excel dates turned into integers?

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


 https://aka.ms/logicexpressions#adddays

Highlighted
New Member

In may email I am receiving the expression only "addDays('1900-01-01',int(items('LWD')?['Date']))". Can you suggest why?

 
Highlighted
Frequent Visitor

Re: Excel dates turned into integers?

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 

Highlighted
Advocate II
Advocate II

Re: Excel dates turned into integers?

@lisacutforth1  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

Highlighted
New Member

Re: Excel dates turned into integers?

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

Highlighted
Advocate II
Advocate II

Re: Excel dates turned into integers?

@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]

 

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/

 

 

 

Highlighted
Microsoft
Microsoft

Re: Excel dates turned into integers?

 
Highlighted
Microsoft
Microsoft

Re: Excel dates turned into integers?

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
Highlighted
Helper I
Helper I

Re: Excel dates turned into integers?

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
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (5,347)