cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EngineeringLion
Frequent Visitor

List rows present in a table outputs Date as Serial Number

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.

 

EngineeringLion_0-1634887737317.png

EngineeringLion_1-1634887777203.png

Output: 

EngineeringLion_2-1634887821539.png

 

 

 

 

6 REPLIES 6
Hardesh15
Super User
Super User

@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

Thank you, did that already, but without effect.

VJR
Super User
Super User

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
Frequent Visitor

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?

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
Regular Visitor

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. 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (2,898)