Converting data from Excel into HTML table as hh:mm

[OliVA2023]

I am currently building a flow which is grabbing data from tables in an Excel spreadsheet, converting them into HTML tables and then sending them out in an email.

I have a 'List rows in a table' function to grab the data from a table which is a rota. The date format is set to ISO 8601.

The table has cells in which 'Shift start' and 'Shift end' times are listed as eg. 08:00 and 16:00. The date format in the spreadsheet itself is 'Custom' (hh:mm). 

I then have a 'Create HTML table' function to convert this rota table into HTML for use in an email. 

However, this results in an email in which the table lists 'Shift start' and 'Shift end' times as a string of meaningless numbers rather than the times I need.

I have tried replacing the 'value' in the 'Create HTML table' to a formula: formatDateTime(item()?['Shift start'], 'hh:mm').

But this causes the flow to fail and the error message below:

The execution of template action 'Create_HTML_table_-_ROTA' failed. The column values could not be evaluated: 'In function 'FormatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

I have been trying to figure this out with Google for the past couple of days and nothing seems to work! 

Any suggestions would be appreciated.

[trice602]

Hi @OliVA2023 ,

The expression is correct.  The issue is your incoming data from Excel.  What is the actual value of a start date?  The "custom format" is for the view, not the actual value.  To convert this with this expression, your excel data needs a fully compatible ISO8601 date and time.

[OliVA2023]

Hi Trice, 

Thank you for taking the time to reply. 

These are the values for the shift start and end times:

If I switch the format to 'General' they change to this:

Slight problem is that this rota table is populated in turn from another spreadsheet, which I do not have the permission to edit. The shift start and end times in this master spreadsheet are also formatted as 'hh:mm' and change to strings of random numbers if you change back to 'General'.

[trice602]

Hi @OliVA2023 ,

Thanks for sharing.  This confirms the root cause - these columns do not contain an actual date/time and that is not going to work well with your expression.

What you need for the expression is an ISO 8601 value here, such as:

12/14/2023 8:00 AM (in any format from your regional use) instead of 8:00.

Here's one option:

For an individual row, such as the first one for 8:00 and 16:15, create a new column here to the far right and you need to concatenate the date for the row with the time so you end up with 12/14/2023 08:00 (again, any ISO8601 format).  Then your expression will work.

To validate and confirm:  Add those extra columns named startdate2 and enddate2, hard-coded a couple of date/times and retest.  FYI:  those "unavailable" times will also throw an error and will need to be addressed after you resolve the ISO 8601 issue.

Please let me know.  💯💯💯💯💯

[OliVA2023]

Hi @trice602,

Thanks again for your help. 

I have created two new columns to test this out and converted the values from the 'Shift start/end' columns into ISO 8601 format using eg. =TEXT(J3, "yyyy-mm-ddThh:mm:ss"):

I have then gone back into Power Automate and asked it to convert these into 'hh:mm' using 

formatDateTime(item()?['ISO 8601 Start'], 'hh:mm').

Unfortunately I am still getting the error message: 

The execution of template action 'Create_HTML_table_-_ROTA' failed. The column values could not be evaluated: 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

[trice602]

Hi @OliVA2023 ,

One issue is the new designer is not coding this correctly which is a BIG ISSUE.  Switch to classic.  Here's my successful test.  It looks like the new designer is not putting this in a for each which causes an error.

My Excel table:  These are "real" date and times, not from a TEXT formula.

My test run:

And my expression:

[grantjenkins]

If you've set ISO 8601, then they should come back as valid dates. I think the issue is that you have a lot of blank lines in your Excel Table so you are trying to use formatDateTime on empty values. Can you try the following expressions in your Create HTML table for your Shift start and Shift end values.

//Shift start
if(empty(item()?['Shift start']),
    '',
    formatDateTime(item()?['Shift start'], 'hh:mm')
)

//Shift end
if(empty(item()?['Shift end']),
    '',
    formatDateTime(item()?['Shift end'], 'hh:mm')
)

[OliVA2023]

Hi @trice602 ,

Thank you again for your continued help!

I suppose then my question is how do I get my new columns to have "real" date and times, that will be recognised as the correct format by Power Automate, given that I'm having to convert from another column's cell values (which in this case are numbers formatted to hh:mm)?

[OliVA2023]

Hi @grantjenkins ,

Thank you for your reply.

I tried the formula below as an expression but it was not accepted:

if(empty(item()?['Shift start']),
    '',
    formatDateTime(item()?['Shift start'], 'hh:mm')
)

Apologies if I have misunderstood.