Column date in Excel equal to today's date

[nraindmia]

Hi all, I'm not good at Power Automate Cloud Flow. 

So this is the situation:

I want Power Automate Desktop to read the column date in Excel. 

Condition: Send an email if the date in Excel plus by '+14 days' equals today's date. 

For example:

Date in excel: 1/4/2023

Supposedly email will be received on 15/4/2023 (because 1/4/223 + 14 days= 15/4/2023)

So how to to set condition in cloud where Date in Excel + by 14 days is equal to Today's date, receive email reminder. 

This is the flow that I tried but didn't work.

formatDateTime(parseDateTime(split(body('Add_14_days'), '.')[0]), 'dd/MM/yyyy')

Thanks !

[v-yueyun-msft]

Hi , @nraindmia 

According to your description, you want to where Date in Excel + by 14 days is equal to Today's date.

So you can judge id the [Start] Date in your Excel file is equal today()-14.

formatDateTime(items('Apply_to_each_2')?['Start'] , 'yyyy-MM-dd')

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

And you need to check if this has selected in your action:

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[nraindmia]

Yes, that is the correct condition butwhen I tried to use your solution:

It did not pass the condition part. 

[v-yueyun-msft]

Hi , @nraindmia 

Can you show the run history's output for your List rows present in a table's Date ?

And what do you put in your Condition?

Best Regards,

Yueyun Zhang

[nraindmia]

[v-yueyun-msft]

Hi , @nraindmia 

Thanks for your quick response! Can you click this to see your [Start] column return in the json , the error code means the [Start] column is not ISO format so it can not use the formateDateTime() function:

Best Regards,

Yueyun Zhang

[v-yueyun-msft]

Hi , @nraindmia 

Oh , i got it why it return error code , you can try to use this code :

formatDateTime(
concat(split( item()?['Start'] , '/')[2],'-', split( item()?['Start'] , '/')[1],'-',split( item()?['Start'] , '/')[0],'T00:00:00Z')
,'yyyy-MM-dd') 

replace the first parameter in your condition:

Best Regards,

Yueyun Zhang

[nraindmia]

formatDateTime(concat(split( item()?['Start'] , '/')[2],'-', split( item()?['Start'] , '/')[1],'-',split( item()?['Start'] , '/')[0],'T00:00:00Z'),'yyyy-MM-dd')

Got this error 😞

[v-yueyun-msft]

Hi , @nraindmia 

I test it in my side , it works good..

Can you click this to see your [Start] column return in the json?

This is the  [Start] column return in my side:

This is code i use in my side :

formatDateTime(concat(split(item()?['Start'], '/')[2], '-', split(item()?['Start'], '/')[1], '-', split(item()?['Start'], '/')[0], 'T00:00:00Z'), 'yyyy-MM-dd')

addDays(utcNow(),-14, 'yyyy-MM-dd')

Best Regards,

Yueyun Zhang

[nraindmia]

Hi, sorry for the late reply,

here is what it looks like from the output

do you mind if you screenshot me your full flow?

[v-yueyun-msft]

Hi , @nraindmia 

This is my full flow, i just have two actions:

And for your error code , it seems that you do not have right to access the Excel file in your side so you do not get the data in your Excel file in Power Automate , you need to check if you use the correct credential in your side :

Best Regards,

Yueyun Zhang

[nraindmia]

I tried to follow your flow, but I got this error. do you know why my start is supposedly a date but in the output, the start number got a different value. 

[v-yueyun-msft]

Hi , @nraindmia 

Can you click this to see your [Start] column return in the json?

It seems in your side , it return difference between us...

Best Regards,

Yueyun Zhang

[nraindmia]

I got a very long JSON. Here i attach the document in word document

[v-yueyun-msft]

Hi , @nraindmia 

Oh , your Start column return  the number in your side :

You can try to use this code in your side :

formatDateTime(concat(split(addDays('1900-01-01',sub(int(item()?['Start']),2),'yyyy-MM-dd'), '/')[2], '-', split(item()?['Start'], '/')[1], '-', split(addDays('1900-01-01',sub(int(item()?['Start']),2),'yyyy-MM-dd'), '/')[0], 'T00:00:00Z'), 'yyyy-MM-dd')

Best Regards,

Yueyun Zhang

[nraindmia]

I got this error

[v-yueyun-msft · Accepted solution]

Hi , @nraindmia 

Thanks for your quick response .. This is due to there is blank in your Start column.

Can you use the FIlter action to filter your [Start] <> "":

Then use this output in your apply to each:

Best Regards,

Yueyun Zhang

[nraindmia]

Hi, it works! I got no error till compose but when I put a condition to send an email if the date+14 days is equal to today's date, I got an error.

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

[v-yueyun-msft · Accepted solution]

Hi , @nraindmia 

Sorry i may put the error code in my first reply, you can use this code :

addDays(utcNow(),-14, 'yyyy-MM-dd')

Best Regards,

Yueyun Zhang

[nraindmia]

Hi,

I have tried and got no error, but

When I tried to put the date 14 days from today (29/11/2023), I did not receive any email. this is my current flow. let me know if my condition is wrong.