Unanswered

List rows present in a table outputs Date as Serial Number

(1) Share

Report

Posted on by EngineeringLion

Hi Experts, I spent three hours in this forum to find a solution for my problem, so I know questions concerning this have been posted quite often, but I could not find a solution for me. Please help.

My problem is that I want to read out an excel file and generate emails with the Information.

I use "List rows present in a table" to read from excel, format date as "ISO 8601" and also formated all excel cells of the required column as Date-Format.

But this action persist to read the date as Serial Number format. What can I do to read it correctly or convert it in a simple way to use it in the email to be created.

Output:

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@EngineeringLion Please flush your browser cache and do not do testing via Resubmit/from previous instances. Close everything and test again.

Excel connector cache data.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

Was this reply helpful? Yes No
EngineeringLion 20 on at

Like (0)

Report
Copy link

Link copied!

Thank you, did that already, but without effect.

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report
Copy link

Link copied!

Hi @EngineeringLion

Since Flow returns the numeric representation of a date you will need to convert it to an appropriate date.

Below video should help you do that.
https://www.youtube.com/watch?v=r1LhHU5lXkM

Was this reply helpful? Yes No
EngineeringLion 20 on at

Like (0)

Report
Copy link

Link copied!

Thank you, but I think there must be a way to get ISO format from the excel directly, therefore this option can be choosen in the List Rows activity. Does it need a special way to be formated in the excel?

Your video was helpful for filtering, but I just want to put the date into mail, is there no possibilty to just change format of display of the date?

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report
Copy link

Link copied!

HI @EngineeringLion

It is not just for filtering. But a way of showing how to convert the numeric date into a proper format.
Would you be able to read the cell value -> change the format as mentioned in video using a Compose statement -> then write it into your email?

Was this reply helpful? Yes No
can-nife 5 on at

Like (2)

Report
Copy link

Link copied!

I know this is late reply and you probably found a solution to all this, but I was also having the same problem as you are.

After doing some test I found the issue and it seems like it will entirely depend on what type of value is at the first cell of the column.

In your case, based on your attached Excel image, the very first cell of your "Deadline" column (R2) is empty which would result in the rest of the cells on that column to become serial number instead of ISO 8601. It must be in a date format; it cannot be anything else.

I don't know why it's like this but here's my solution based on your images.

formatDateTime(
addDays(
'12-30-1899',
int(items('Apply_to_each')?['Deadline']),
'MM-dd-yyyy'
),
'MM-dd-yyyy'
)

Maybe due to my lack of knowledge, but I've noticed that the serial number is the total amount of days starting from a certain point in time, that being 12-30-1899.

Was this reply helpful? Yes No
heckmann 4 on at

Like (0)

Report
Copy link

Link copied!

That expression did it for me... Thanks

Was this reply helpful? Yes No