cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dngtie
Level: Powered On

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
Community Support Team
Community Support Team

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

10 REPLIES 10
Community Support Team
Community Support Team

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

dngtie
Level: Powered On

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.

dngtie
Level: Powered On

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!

crf200
Level: Powered On

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']))
MateiC
Level: Powered On

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
rodieremix
Level: Powered On

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. 

AS1
Level: Powered On

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!

crf200
Level: Powered On

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

AS1
Level: Powered On

Re: Excel dates turned into integers?

Thank you for your quick response!

I solved it!

 

dangolra
Level: Powered On

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 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,567)