Excel dates turned into integers?

[Anonymous]

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!

[v-yamao-msft · Accepted 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

[Anonymous]

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

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 · Accepted solution]

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']))

[MateiC]

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

[Anonymous]

---

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

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!

[crf200]

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]

Thank you for your quick response!

I solved it!

[Anonymous]

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 

[hjaf]

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

 https://aka.ms/logicexpressions#adddays

[lisacutforth1]

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 

[RachelRig]

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

[bloomwoz]

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

[RachelRig]

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

[JaredRic]

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:

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

[khaycock]

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?

Thanks!