Trouble with ISO 8601 and formatDateTime()

[noobiPowerAuto]

Hi, 

I'm trying to make a flow that sends an email if there's a date approaching whiting 30 days from excel's sheet.

But when a set everything up and run the flow a get the following error: (PS: I'm a Portuguese speaker, so my flow is in Portuguese but I'll translate it for you guys)

It says that my input format won't fit formatDateTime because it isn't set as ISO 8601. 

my sheet 

and now my flow

and, those are the functions used:

(1) formatDateTime(addDays(utcNow(),31),'yyyy-MM-dd')

(2)formatDateTime(items('Aplicar_a_cada')?['Início'],'yyyy-MM-dd') 

number 2 in English is usually set as: formatDateTime(items('Apply_to_each')?['Início'],'yyyy-MM-dd')

Can somebody give me a hint so I can finish this flow? I'm a new user of it.

Thanks in advance

[ccc333ab]

When you bring the Excel date across, it comes across as an Integer date as I believe its actually stored that way in Excel. You will need to convert to a string format by doing the following:

addDays('1899-12-30', int(item()['Início']), 'dd-MM-yyyy')

Your 

[noobiPowerAuto]

Hi,

I tried to input it like 

formatDateTime(addDays('1899-12-30',int(item("Aplicar_a_cada')?['Início'])),'yyyy-MM-dd')

. because I'm swipping many rows, but PA won't accept it, it says it's an invalid expression.  Was that the way you were suggesting?

[noobiPowerAuto]

FormatDateTime(addDays('1899-12-30',int(items('Aplicar_a_todos')?['Início']),'yyyy-MM-dd')) 

I also tried that and got this warning:

It says it's an invalid template, it's invalid when using 'apply_to_each' and it's not defined model 

[ccc333ab]

Use this: 

addDays('1899-12-30', int(items('Apply_to_each')?['OtherDate']), 'yyy-MM-dd'). 

You don't need to include the formatdate since addDays formats it into the correct format you need. 

[noobiPowerAuto]

Hello, thank you for clarifying but it still doesn't work. I got the following error 

when I inputed as you suggested(changing only the variable 'otherdate' and apply_to_each) :

addDays('1899-12-30', int(items('Aplicar_a_cada')?['Início']), 'yyy-MM-dd')

It says that the argument inside of "int()" is not valid 😕

Do you have any other suggestions?

[ccc333ab]

OK, so can you do the following so we can see what is being returned. 

• Just before the condition, can you add in a compose statement, and in the input just put the expression: items('Aplicar_a_cada')?['Início']. 

[noobiPowerAuto]

here is what it looks like 

[ccc333ab · Accepted solution]

That is weird, you are getting actual dates from Excel. So you don't need to convert it from integer date (not sure I've seen this before...). 

So I think we are back to your original question so can you do two additional things: 

1. Create a new compose, just before the one I had you create and cut/paste the expression you used for the utcdate (the left hand side of your condition). 

2. In the compose I just had you create, put in formatDateTime(items('Aplicar_a_cada')?['Início'], 'yyyy-MM-dd') 

[noobiPowerAuto]

Hello, 

Thanks a lot!! When you commented that it was already with actual dates automatically, I remembered that I have set to do so by changing advanced options. when importing the excel sheet, the last option (dateFormat) was set as ISO 8601. This option does that, it imports all dates in its own format. I thought it would make it easier, but it actually didn't. I just needed to set it as "serial number" and it was solved using your suggested input!

thanks again, you helped me a lot with this issue.

[ccc333ab]

OH, totally forgot about that setting. Hmm...you'd think that would have worked for you in the first place. Glad to hear you have it working....best of luck.