Unanswered

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

(0) Share

Report

Posted on by kev1234

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

Categories:

Using flows

I have the same question (0)

All responses (5)

Answers (1)

Dev_Nikhil 225 on at

Like (0)

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

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

Was this reply helpful? Yes No
kev1234 30 on at

Like (0)

Report

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

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?

Was this reply helpful? Yes No
Verified answer

kev1234 30 on at

Like (0)

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

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.

Was this reply helpful? Yes No
vecerpa 786 on at

Like (0)

Report

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

Hello @kev1234 ,

You are welcome.

And have a nice Flowing 🙂

P.

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

Like (0)

Report

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

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

Was this reply helpful? Yes No