I get a date value from Excel in the form 43201 as an example.
How can I make a date value out of this number that I can use in a condition to evaluate whether this date is less or greater than the current date?
Would be great if somebody could help.
Hi @ lreinhard7,
Do you want to check if the value of date column in the excel table is less or greater than the current date?
I have a date column "starttime" in the excel table, and you could refer to screenshot below to create the flow to check if the value of date column in the excel table is less than the current date.
The expression in the Condition as below:
The flow would run successfully as below:
If you would like to check if the value of date column in the excel table is greater than the current date, you could use the expression in the condition as below:
You could refer to link below to learn more about the workflow definition language:
I tried this using the suggestion
After a lot of trial and error, I think I have a solution: ticks!
Here's how you construct a date taken from Excel Online:
(I'm taking the date from a loop, but feel free to get it from elsewhere)
And here's how you turn today into ticks:
(you can remove startOfDay if you care about the time, too)
What's going on with the first function: You take Excel's numeric date (days since 1/1/1900), e.g. 43267.234234826 as a float, then multiply it by 864000000000 to turn it into 100s of nanoseconds (i.e. ticks) and, finally, add the difference between 1/1/1900 and 1/1/0001 (in 100s of nanoseconds)
I am having similar issues but I'm also trying to convert the time (e.g. a serial like 43598.802997685 should read 13/05/2019 19:16). I've got the purely the date working, but can't get the time as well.
For context, I'm importing a load of stuff from an excel table into a SharePoint list.
Thank in advance!
AddDays can only add (or substract) whole days. By using int(), you strip your date of the time (but it's mandatory for addDays().
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.
It's going to get a bit long. Good luck!
Look out for new contribution recognition badges coming SOON!
Join us for the first ever Power Platform Online Conference!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!