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

All responses (6)

Answers (1)

TonyCastro75 4 on at

Like (0)

Report
Re: HTML Table, Time value is showing up incorrectly .

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.
Verified answer

efialttes 14,756 on at

Like (0)

Report

Re: HTML Table, Time value is showing up incorrectly .

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
Alnng0 9 on at

Like (0)

Report

Re: HTML Table, Time value is showing up incorrectly .

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,
efialttes 14,756 on at

Like (0)

Report

Re: HTML Table, Time value is showing up incorrectly .

Hi again

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

Thanx!
Alnng0 9 on at

Like (0)

Report

Re: HTML Table, Time value is showing up incorrectly .

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
efialttes 14,756 on at

Like (0)

Report
Re: HTML Table, Time value is showing up incorrectly .

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