Vacation flow - problem with date convertion (Excel Online)

[bireta]

Hello!

I am new to creating flows. But after few hours of trying I stopped on a weird error.

I use Excel Online on Sharepoint to store new and approved requests.

My approval and then mail confirmation include start and end date of the vacation.

Flow works fine but I have numbers instead of date (eg. 43945.66666667 instead of 24.04.2020).

What could I do with that? I've tried using expressions:

if(empty('Data rozpoczęcia'),'',addDays('1899-12-30',int('Data rozpoczęcia'),'MM/dd/yyyy'))

But when i put them instead of Excel Items it gives me that error:

It cannot be converted. So what else could I do to have a date, not numbers?

My Approval looks like that:

Is it wrong to use an expression like that? What else could I do to have a correct date format in the approval e-mail and confirmation e-mail at client's inbox?

Thank you in advance for any kind of help! : )

BR, Bireta

[ChristianAbata]

hi @bireta  this data inside needs to be a number

int('Data rozpoczęcia') not empy or characters

please to test, try addind a compose action before your condition and inside add just Data rozpoczęcia, to see what kind of value do you have, because the error says that the parameter are you trying to convert is not valid.

[bireta]

@ChristianAbata Thank you for a quick response

I would like to know if I got it clear (been looking for the correct action for a while).

Is it one of those?

I made it as 'add' but what should I put in 'from'? It doesnt't see any dynamic content over there.

[bireta]

@ChristianAbata 

Ok, i completly did not understand what you tried to say - now I know that : )

Look, I don't get how those expression work at all - I copied that from other topic.

What do you mean it has to be a number? Like in Excel Online the columns with Start/End Date should be a number stuff?

Now my expression looks like that (maybe that would be better, with int(item() ?):

addDays('1899-12-30',int(item()?['Data rozpoczęcia']),'yyyy-MM-dd')

But how can i convert those 3 elements? I found a similar problem and guy made it with those options:

But he has a 'De' which is 'From' line - what can i put there? There is nothing possible to write over there.
Where did he get this 'Excel Icon value' thing? Mine looks like that and it doesn't hint me with any dynamic content:

Thank you in advance

[v-litu-msft · Accepted solution]

Hi @bireta,

Since the Date from Excel table is a Float, so you should use split() function to remove these numbers after dot, then get the integer part, convert it into an integer by using int() function. Then use the addDays just as your previous reply:

addDays('1899-12-30',int(first(split('43945.66666667','.'))),'yyyy-MM-dd')

Run result:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bireta]

@v-litu-msft Thank you for the reply

But the problem is I don't want to change it manually every time someone drops a new vacation request.

I need it to be a constant value converter. I will start again, slowly:

When someone requests their vacation from 27.04 to 01.05 - I need it to show the correct date format and hour.

It looks like that: Someone puts vacation request in Table1 in Excel Online -> then a Flow starts (with 1 hour recurrence).

It gets the row with the new request and waits for the Approval.

I need the correct date (dd-mm-yyyy, hh:minmin) format in the Approval email to the Department Manager first.

If the Manager approves the request - it comes back to the employee saying his Vacation Request has been approved and it starts from XX.XX.XXXX, YY:YY and finishes on ZZ.ZZ.ZZZZ, VV:VV.

The approved request also goes to another Table2 and shows all the vacation requests that have been approved.

Later on I will put this in someones Outlook Calendar automatically, so it has to be the correct date, so the calendar can show it well.

But I need the flow to make it not numbers like 43945 but the date+time. Is it possible?

Thank you!

[v-litu-msft · Accepted solution]

Hi @bireta,

Yes, it is possible, though it is a little complex, it could be done.

Perhaps you can use the decimal part of the date number and multiply it by 24 (and get the hours), then use the new decimal part and multiply it by 60 (and get the minutes) and, finally, take the last decimal part and multiply again (and round) by 60 (to get the seconds).

For example, 43598.802997685 -> 0.802997685 * 24 = 19,27194444 -> 0,27194444 * 60 =16,3166664 -> 0,3166664 * 60 = 19.

So the whole thing would become 13/05/2019 19:16:19

You can use int() to get the non-decimal part, then sub() it from the initial number, then mul() it by 24/60 and so on.

This method is provided by @ymaglaras 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.