Answered

Excel dates turned into integers?

(3) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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!

Categories:

Building flows

I have the same question (0)

All responses (36)

Answers (2)

Verified answer

v-yamao-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

crf200 96 on at

Like (19)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
MateiC 48 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (6)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
crf200 96 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you for your quick response!
I solved it!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No