Unanswered

Excel Value is coming as a number

(0) Share

Report

Posted on by Jonas4

Hello, i got a Power Automate Flow where I read out an excel list and which contains many different values but also some date values and I pass it into a Sharepoint list. In the excel list formatted all of the columns as a date in the same format and I choosed the ISO 8601 DateTime format in Power Automate. The first 4 columns are formatted right but the next 4 are formatted as numbers. I tried some different things but I dont know how to fix it. I heard of the function FormatDateTime that can convert the numerical values into date values but the problem ist that I already use this function "if(or(equals(outputs('Auftragsstart'),''), equals(outputs('Auftragsstart'),'#NV')), null, outputs('Auftragsstart'))" to leave the Sharepoint column blank if the excel column was blank because otherwise there was an error. For that I first use compose actions where I put in the single date values. Does someone know either a solution that all values from excel come as a date or if I let all date values come as a number how to rewrite the function so that Im using the FormatDateTime combined with the function that if a column is empty.
If i didn't mentioned something important just ask then i will answer

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Jonas4 4 on at

Like (0)

Report
I just found this code
if(empty(item()?['Timesheet Completed Date']),null,addDays('1899-12-30',int(item()?['Timesheet Completed Date']),'yyyy-MM-dd'))
which works with a compose but with that code I got the problem that it says "" isn't a date/string type still if i use this function
"if(equals(outputs('Verfassen'),' '),null,outputs('Verfassen'))"
("Verfassen" is my compose, where I'm converting the number to a date with the first code)
I don't know why the if is not working because as I did with the ISO8601 format it worked for me.

Was this reply helpful? Yes No
Jonas4 4 on at

Like (0)

Report

Doesn't makes sense to me because both fields are "" but the "Link" is null then and the "Verfassen" is still "".
This is the code i used for "Link"
if(equals(outputs('Link'),''),null,outputs('Link'))
This is the code I used for "Verfassen"
if(equals(outputs('Verfassen'),' '),null,outputs('Verfassen'))

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
I made a post that relates to this issue.

When Excel first row has blank date all remaining row dates will be Serial instead of ISO 8601

Basically you will need to use the date as Serial the entire time, not ISO 8601, for your function to work properly.

This is the example expression I used in my post.

if(empty(item()?['Birth Date']),null,addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['Birth Date']),86400),'0'))))

Was this reply helpful? Yes No