List rows present in a table outputs Date as Serial Number

[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: 

[Hardesh15]

@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

[EngineeringLion]

Thank you, did that already, but without effect.

[VJR]

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

[EngineeringLion]

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?

[VJR]

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?

[can-nife]

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. 

[heckmann]

That expression did it for me... Thanks