![]() ![]() To get a date from Excel in your Power Automate flow you’ve got two options. As the formatNumber(…) expression would round the results, convert them all into string(…) before splitting them by the dot and taking only the first part. Put it all together and build the date including the time. …and then seconds from the minutes number. Since it’s another decimal number, repeat the process to extract also minutes from the hours number… div(float(concat('0.', split(string(outputs('Compose_2')),'.'))),div(1,float(60))) The result will be another decimal number – a number of hours e.g. You must use the float(…) expression in the division to get a decimal result. …and divide it by 1/24, the decimal representation of 1 hour. …add leading zero and convert it into a decimal number using the float(…) expression… float(concat('0.', split(items('Apply_to_each')?,'.'))) ![]() addDays('',int(split(items('Apply_to_each')?,'.')),'yyyy-MM-dd')īut if you want also the time, you’ll need a few more calculations to extract it from the decimal number.įirstly, extract the decimal part from the number… split(items('Apply_to_each')?,'.') Split(…) it by the dot and keep only the first part, e.g. Therefore, if you need only the date, without time, you must remove the decimal part of the number before the calculation. That’s a problem as you can add only full number of days. If the column contains date and time it’ll return a decimal number. addDays('',int(items('Apply_to_each')?),'yyyy-MM-dd') Convert date and time column into date Take the number and add it as days to the “base date”, e.g. It’s much easier for a date only column as it provides a whole number. Although it’s a bit different depending on the date format – if it’s a date only or date and time. To convert it into a date just add the days to the date. The number as provided by Excel is a number of days since the 30th of December 1899. It’s an alternate approach in case the ISO conversion doesn’t work (as happened in one of my flows). The other, more complicated option is to convert the number into a date. Click on the ‘Show advanced options’ and switch the DateTime Format to ‘ISO 8601’. The easiest approach is to switch the date directly in the ‘List rows…’ action. How do you convert it into a date to work with it as a date? Switch the action to ISO 8601 format It’s correct, but it’s not the value you need (unless you’re building a reminder). If you store only date, without time, it’ll return a whole number if you include also time you’ll get a decimal number. And that’s also what the ‘List rows present in a table’ action returns – a number. It’s not in the ISO format year-month-date, but a sequence number. Unlike most data sources, Excel stores dates in a specific way. “I have an Excel file with dates in one column, but Power Automate returns a number instead of the date, how can I fix it?” ![]()
0 Comments
Leave a Reply. |