Converting dates from Excel Sharepoint sheet to dates instead of integers

(0) Share

Report

Posted on by Ellen1981

Hi there,

after looking at tons of youtube videos and community posts, I finally give up; I appreciate any help.

I have an excel table in One Drive and want to send an email extracting dates from the excel file.

The issue that I have is that the date extracted from the one column are showing up in the email like '44789'.

Excel online: Key column is called 'Datum vom letzten Code'

Details for 'Apply to each': Formular is formatDateTime(addDays('1900-01-01',add(int(outputs('Compose')),-2)),'dd.MM.yyyy')

After running this I get the following error:

thank you for your time,

Ellen

Categories:

Using flows

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Manish Solanki 15,169 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Ellen1981

To get the dates in proper format from the output of 'List rows present in a table' action, please perform the following steps:
1. Click on the link 'Show advanced options':

2. Set 'DateTime Format' parameter to 'ISO 8601' as shown below:
After making those changes, you will get dates with time zone value in the format 'yyyy-MM-ddT00:00:00.000Z'. To further change its format, you can make use of formatDateTime formula.

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
Ellen1981 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Manish,
thank you for getting back to me so quickly.

I added ISO 8601 to "List rows present in a table'

When I test the flow, I see that in the Output of "List rows present in a table" that the column "Datum vom letzten Code" is still shown in dates instead of the ISO format.
Not sure if it makes a different but this excel column has a formula embedded to calculate a specific date based on the time the person completes a Microsoft Form.

I also receive this error message. You can see that there is no information for INPUTS or OUTPUTS. Does this cause another issue as well?

And if this is not the reason, can you tell me which date format I have to use? I typed in these expression in the section 'Compose2':
formatDateTime(utcNow()outputs('Compose'),'dd.MM.yyyy') or
formatDateTime(outputs('Compose'),'dd.MM.yyyy')
and none of them seem to work.

Thank you
Ellen

Was this reply helpful? Yes No