cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Power Participant
Power Participant

Excel Online Date

Hello,

 

I have question if there is a chance to compare Date from Excel Online Business connector to utcnow().

 

The scenario is that I'm loading excel table via connector Excel Online Business

 

ExcelOnline1.png

 

I see that the format that is being loaded into Flow is

 

 ExcelOnline2.png

 

In excel there is correctly displayed

ExcelOnline3.png

 

So I'm wondering how to compare it against utcnow(), what transformation I need to perform before I can use expression like utcnow in between StartDate and EndDate that is loaded from Excel sheet.

 

Thank you for help

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Power Participant
Power Participant

Re: Excel Online Date

Hi @v-yuazh-msft,

 

I found workaround for this as the number that is being delivered from excel online is count of days since 01/01/1900, I created variable that adds number delivered from excel online to this date and now I can work with it inside Flow 🙂

 

addDays('1900-01-01T00:00:00Z',int(item()?['Start Date']))

P.

View solution in original post

Highlighted
Community Support
Community Support

Re: Excel Online Date

Hi @vecerpa,

 

I find that the expression that you mentioned would get a date that is two days more than the date in the Start Date column:

addDays('1900-01-01T00:00:00Z',int(item()?['Start Date']))

Maybe you need to add a sub() to the expression.

 

Best regards,

Alice

View solution in original post

Highlighted
Power Participant
Power Participant

Re: Excel Online Date

Hi @v-yuazh-msft,

 

You are right.

 

I was digging for a while how Excel is storing date and I found out that http://www.cpearson.com/excel/datetime.htm. So now it is more clear that this number that is comming from Excel is really specific 🙂

So the corrected expression is below

addDays('1900-01-01T00:00:00Z',sub(int(item()?['Start Date']),2))

Thank you

P.

View solution in original post

15 REPLIES 15
Highlighted
Community Support
Community Support

Re: Excel Online Date

Hi @vecerpa,

 

I have made a test on my side and I afraid that there is no any way to compare a date from Excel Online Business connector to utcnow().

 

The date form  Excel Online Business connector  are always with format like "43225", and there is no any way to format it to ISO 8601 format and than compare with utcnow().

 

If you want to date in the Excel Online Business connector would be a ISO 8601 format, and the you could compare the date with utcnow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

 

As an alternative way,If you want to format a date in excel with utcnow(), please save the excel table in Onedrive for business , and the use the Excel connector instead of Excel Online Business connector to achieve your needs.

 

Best regards,

Alice

Highlighted
Power Participant
Power Participant

Re: Excel Online Date

Hi @v-yuazh-msft,

 

I found workaround for this as the number that is being delivered from excel online is count of days since 01/01/1900, I created variable that adds number delivered from excel online to this date and now I can work with it inside Flow 🙂

 

addDays('1900-01-01T00:00:00Z',int(item()?['Start Date']))

P.

View solution in original post

Highlighted
Community Support
Community Support

Re: Excel Online Date

Hi @vecerpa,

 

I find that the expression that you mentioned would get a date that is two days more than the date in the Start Date column:

addDays('1900-01-01T00:00:00Z',int(item()?['Start Date']))

Maybe you need to add a sub() to the expression.

 

Best regards,

Alice

View solution in original post

Highlighted
Power Participant
Power Participant

Re: Excel Online Date

Hi @v-yuazh-msft,

 

You are right.

 

I was digging for a while how Excel is storing date and I found out that http://www.cpearson.com/excel/datetime.htm. So now it is more clear that this number that is comming from Excel is really specific 🙂

So the corrected expression is below

addDays('1900-01-01T00:00:00Z',sub(int(item()?['Start Date']),2))

Thank you

P.

View solution in original post

Highlighted

Re: Excel Online Date

I have the same problem but may I see how you do this on flow? I'm getting errors regarding not being able to parse integer when the excel we are using is clearly a datetime format.

Highlighted
Power Participant
Power Participant

Re: Excel Online Date

Hi @KarlChristopher

I am not parsing integer, I am just converting number comming from excel to int. Such integer I am using in adddays function as number of days (it have to be in integer format) that will be added to 1900-01-01T00:00:00Z. And as @v-yuazh-msft mentioned there is need to add two more days, as there is specific number comming from excel. So thats all.

P.

Highlighted

Re: Excel Online Date

Managed to fixed it by not using a custom data type. Somehow it does not work when I add time in my excel date? What do you think would be a good workaround for this?

 

it gets 45333.3xx or something, then I get the int error from Flow.

Highlighted
Frequent Visitor

Re: Excel Online Date

you change my day, thank you for the solution

Highlighted
Frequent Visitor

Re: Excel Online Date

Hi Vecerpa,

 

I am also trying to compare excel date with today's date. I still can't figure out how you apply your solution?

From the picture, the left side is the date value of "43531" from Excel Online (Business) connector. So how do i compare it with today's date? I was thinking of using utcnow, but how do i convert it to days?

 

Appreciate your help in this. Thanks.

 

Kevin 

Highlighted
Power Participant
Power Participant

Re: Excel Online Date

Hello @kev1234 ,

 

It is described in accepted solution for this chain.

addDays('1900-01-01T00:00:00Z',sub(int(item()?['NOTiFY_...']),2))

There is need to use this adddays function that will add number of days that are in your case 43531-2 that means 43529 days to date 1.1.1900 and this compare against utcnow that means actual date.

 

So you have to replace left side of your conditon by this function that is above. You have to replace item()?['NOTIFY_...'] by item that is returning you number 43531.

P.

Highlighted
Frequent Visitor

Re: Excel Online Date

Hi @vecerpa ,

 

Now i understand. Thank you so much for the clarification. I really appreciate it.

 

So below is my solution after applying your formula:

 

Left side of the equation

formatDateTime(addDays('1900-01-01',sub(int(item()?['NOTiFY_6M']),2)),'yyyy-MM-dd')

 

Right side of the equation

formatDateTime(utcNow(),'yyyy-MM-dd')

 

So both side will return a date string with the format of 'yyyy-MM-dd', e.g. 2019-03-07.

 

Thanks again @vecerpa 

 

Highlighted
Frequent Visitor

Re: Excel Online Date

Good afternoon,

 

I did try with this solution and it is not working for me. I am getting a conversion error when trying to convert the data string item()?['Date_column_name'] to int. Error is "int function is getting a not appropriate input value".

 

How do you think I can fix this?

Thank you

Highlighted
Power Participant
Power Participant

Re: Excel Online Date

Hello @Stefano__ 

What does return your action that is loading data from excel file? As Microsoft Flow is evolving it is possible that this was changed.

P.

Highlighted
Frequent Visitor

Re: Excel Online Date

@vecerpa i see it has been some time since this has been accepted but i've been struggling a few hours now to get this process but am getting multiple errors. I'm wondering if you still have or could set up a similar situation and share screen shots? 

 

I'm also happy to share the problems im having and work down that route if that is preferred, thanks! 

Highlighted
New Member

Re: Excel Online Date

If it is DateTime from excel online, getting parse error. How to proceed with DateTime?

My excel sheet sample

startdateissue.PNG

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

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,877)