HTML Table, Time value is showing up incorrectly .

(0) Share

Report

Posted on by Alnng0

Hello,

I have an excel spreadsheet hosted on Sharepoint.

My flow is converting the excel spreadsheet to an HTML table and then emailed out every 8 hours.

I know excel online doesn't correctly output the date/time .

I was able to separate the date into another column and it works fine but the time is still showing up as an integer.

Is there an expression I can implement to convert this?

thanks,

Screen Shot 2020-02-10 at 10.40.36 AM.png

Categories:

Using flows

I have the same question (0)

All responses (6)

Answers (1)

efialttes 14,756 on at

Like (0)

Report
Hi

The HTML column displayed with wrong format is the Fifth one, right?

Is the source Date column on your excel in Date format? If so, the fastest way to proceed is to convert it in excel to text format

THe reason is that, according to this post, the date calendar in Excel is an integer representing the number of days since January 1st, 1900. Please also note, that, according to this web site:

Days Since 1900-01-01 (+2) is used in the Microsoft Excel date functions such as DAY(serial_number) and WEEKDAY().
Note, Excel's serial number is 2 higher than the number on this page.

In Excel January 1 is serial number 1. In this converter midnight January 1, 1900 is 0, after 1 day it is midnight on January 2. To correct this you have to add/subtract 1.

Excel incorrectly sees 1900 as a leap year (for compatibility with Lotus 1-2-3) so you have to add/subtract 1 to all days when using in Excel. Also see: Excel incorrectly assumes that the year 1900 is a leap year

If you shall mantain the excel column Date format, you will need to add an expression to translate the Date in integer format into the PA flow Date internal format. If you need it, please let us know

Hope this helps

Was this reply helpful? Yes No
Alnng0 9 on at

Like (0)

Report

It's the second column on the HTML Table above listed as Time.

The format for the column in the excel spreadsheet was already set to Text

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report

Hi again

What's the expression you used to split Date and Time?

Thanx!

Was this reply helpful? Yes No
Alnng0 9 on at

Like (0)

Report

On the original Excel spreadsheet, I just have the date separated on its on Column by using the function: =MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2).

and for the Time column I have the time separated by using the function: =TIME(HOUR(A2),MINUTE(A2), SECOND(A2))

and in Flow i didn't need to do anything and it list the date on the HTML Table just ffine

the variable is just a
cell style, ( variables('varStyle').cellStyle)

which is:
cellStyle": "style=\"font-family: Arial; padding: 5px; border: 1px solid black;\"",

With that the HTML table comes out looking like:

So that is all I did.

Thanks,

Was this reply helpful? Yes No
Verified answer

efialttes 14,756 on at

Like (0)

Report

Hi again
I am not an excel expert, but according to this post:
The Microsoft Excel TIME function returns a decimal number between 0 and 0.999988426 given an hour, minute and second value. A result of 0 represents 12:00:00 AM and a result of 0.999988426 represents 11:59:59 PM. The TIME function is a built-in functionin Excel that is categorized as a Date/Time Function.
https://www.techonthenet.com/excel/formulas/time.php
So, the format displayed by your flow sounds consistent with the excel formula you chose
Hope this helps

Was this reply helpful? Yes No
TonyCastro75 4 on at

Like (0)

Report
Hello, I am new here and with P.A I have a column with the time 12:12:12 for example in an excel, when I want to send it to the mail by HTML I cannot find the expression that does it. I tried many and I can't. Thanks for if you can help me.

Was this reply helpful? Yes No