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

Excel online (business) converts date to days. So how do i compare it with today's date?

Hi experts,

 

I'm trying to compare a date (from excel online (business)) with today's date. From the picture below, 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 

1 ACCEPTED SOLUTION

Accepted Solutions
kev1234
Frequent Visitor

Found the answers to my questions. Thanks to @vecerpa from this post below:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/m-p/247673#M24544

 

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.

View solution in original post

5 REPLIES 5
Dev_Nikhil
Advocate III
Advocate III

Hi @kev1234 ,

Yes you can use "utcnow()" with some Twists like below

utcnow('dddd')

You just have to put it into the Regular expression and you will get the Today's Day value.

 

For another operations i would like to suggest you a Wonderfull blog post please refer it specially when you will be working on Date and Time Into Microsoft Flow.

https://www.o365dude.com/2017/05/01/working-with-dates-times-in-microsoft-flow/

Hope this will solve your Issue.

 

Please accept this as solution if it solves your problem.

 

Thanks & Regards,

Dev_Nikhil

 

 

Hi @Dev_Nikhil ,

 

Thanks for your reply. 

 

I think i wasn't clear in my question. I implemented a connector 'List rows present in a table' from Excel Online (Business) that pulls in the data collection. However the date value is being converted to total days since '1-1-1900' + 2 days. See below.

 

 

 = 43531 (which is the total days since '1-1-1900' + 2 days)

 

So the question is, how do i compare 43531 days with today's date? I would like to send an email notification when the date in the excel column 'NOTIFY_6M' match with today's date.

 

FYI, i encountered sign-in issues when using other connectors like 'Excel' and 'Excel Online (OneDrive)'. So the only option i have was 'Excel Online (Business)'.

 

Anyone?

kev1234
Frequent Visitor

Found the answers to my questions. Thanks to @vecerpa from this post below:

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/m-p/247673#M24544

 

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.

View solution in original post

vecerpa
Memorable Member
Memorable Member

Hello @kev1234 ,

You are welcome.

And have a nice Flowing 🙂

P.

@kev1234 So instead of the Condition you have in the initial post, this is the one you used? Also is this right after the List Rows Present in Table step?

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (61,368)