Answered

Number fields from Excel showing as Date in array

(0) Share

Report

Posted on by JWessel

There are two columns of data in my attachment where the values displayed appear as dates but should be numbers. The values in the Excel table are set to Number format, just like the preceding columns on the attachment. The array is populated in the flow via the Append to array variable as the action.

Any thoughts on why these last two would show up as date values and not integer numbers?

Categories:

General topics

I have the same question (0)

All responses (12)

Answers (1)

Sort by

jiwhite 500 on at

Like
a
(0)

Report
Copy link

Link copied!

Please post an anonymized version of your flow so that we can see how your data is being processed. The attached spreadsheet has date text in the columns and is formatted as General, not Number, so it should be carried through as dates.

Was this reply helpful? Yes No
JWessel on at

Like
a
(0)

Report
Copy link

Link copied!

Hi jiwhite. Thanks for quick reply. The attached spreadsheet is the flow output. Apologies for the ignorance, but how does one create an anonymized version of the flow?

Was this reply helpful? Yes No
JWessel on at

Like
a
(0)

Report
Copy link

Link copied!

sorry, I misunderstood and believe you mean anonymized data and I will do that.

Was this reply helpful? Yes No
JWessel on at

Like
a
(0)

Report
Copy link

Link copied!

jiwhite, the source data for all of the columns in the Redeemed Report attachment for "Twin Mattress through the "Pack-N-Play" column are all formatted as number. The report itself, a .csv output, also has columns G through N as General format and those numbers show up fine. It only seems to be the numbers in columns M and N on the output. For example on the output row with the name of Timaya Harris (Excel row 32), the source data for the Crib column and Pack-N-Play column contains the value of 0 (zero) and is a number format field.

Was this reply helpful? Yes No
JWessel on at

Like
a
(0)

Report
Copy link

Link copied!

Attaching new source file, anonymizing data.

Rahe Fund and Inv...

Was this reply helpful? Yes No
JWessel on at

Like
a
(0)

Report
Copy link

Link copied!

flow steps
1.
2.
3.
4.
Group 1
condition 1 = Redeemed y or N = y
condition 2 = if(empty(item()?['Redeemed Date']),1,0) = 0
Group 2
condition 1 = greaterOrEquals(addDays('1899-12-30',int(item()?['Redeemed Date']),'yyyy-MM-dd'),subtractFromTime(convertFromUTC(utcNow(),'Eastern Standard Time'),1,'Month','yyyy-MM-dd'))
condition 2 = lessOrEquals(addDays('1899-12-30',int(item()?['Redeemed Date']),'yyyy-MM-dd'),subtractFromTime(startOfMonth(convertFromUtc(utcNow(),'Eastern Standard Time')),1,'Day','yyyy-MM-dd')) is TRUE

5.
Compose with expression = sort(variables('HtmlTable'),'Conference')

6. Create csv table with Outputs from Compose step
7. Last step is to send email V2

Was this reply helpful? Yes No
jiwhite 500 on at

Like
a
(0)

Report
Copy link

Link copied!

Anonymizing the flow just means black out any portions of urls, labels, etc., that reference personal or corporate information that you don't want revealed.

Was this reply helpful? Yes No
Verified answer

jiwhite 500 on at

Like
a
(0)

Report
Copy link

Link copied!

There are dates in the columns a couple of columns over. Are you sure that you've defined the output from the Office Script correctly? My guess for why you're getting dates sent back would be that you accidentally sent the wrong columns back from the spreadsheet. Can you share your Office Script?

Was this reply helpful? Yes No
JWessel on at

Like
a
(0)

Report
Copy link

Link copied!

I don't have an office script. Source is just a table (Table2) defined on one sheet of a workbook and identified in the "List rows present on a table" step.

Was this reply helpful? Yes No
JWessel on at

Like
a
(0)

Report
Copy link

Link copied!

Well, I'm not sure exactly what happened but a flow that failed 2 hours ago now works. I wish I knew what it was in the data that changed because the flow didn't change. Maybe it took some time to refresh the file on one drive or something. Anyway, I'll mark as accepted at this point and hopefully figure out exactly what it was that changed through versioning or something. 🙂

Was this reply helpful? Yes No