Unanswered

Inserting date/time into Excel

(0) Share

Report

Posted on by lburmz

I have a flow that parses information from the body of an email and adds it in a new row in excel. The problem that I am having is that the date/time is going in as a string.

The date/time comes from the first line of the email: "An order was placed for the following product(s) on 17/03/2022 20:28:14."

This is what I'm doing:

1. Pull out the date and time

2. Initialize a date/time variable as string

3. Split into date and time

4. Split the date into dd, mm and yyyy components

5. Create a string combining the date and time components into ISO format (I have tried with and without formatDateTime() - makes no difference)

6. Insert into excel

This all works well, except that the date and time go in to excel (on sharepoint) as a string (align to the left):

If I then double click on the relevant cell it turns in to a date.

If anyone can suggest how to ensure that this goes in as a date, or can suggest a better way of constructing the date and time, I appreciate any advice.

Categories:

Using flows

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

v-jefferni on at

Like (0)

Report
Hi @lburmz ,

You will need serial numbers to save to Excel datetime column:

add(div(sub(ticks('03/17/2022 20:28:14'),ticks('1900-01-01T00:00:00Z')),864000000000),2)

https://manueltgomes.com/microsoft/convert-date-to-excel-number/

https://ryanmaclean365.com/2020/07/28/converting-excel-date-time-serial-values-with-power-automate/

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
lburmz 28 on at

Like (0)

Report

Thank you, I'll try that.

Surely though the whole point behind Microsoft adding the ISO format capability to Excel is so that you don't have it convert it to a serial number?

Was this reply helpful? Yes No
lburmz 28 on at

Like (0)

Report

Hi again. So that does mostly work, but it doesn't include the time, only the date. For example, what I need is '26/03/2022 16:47:51' and what I'm getting is '26/03/2022 00:00:00'

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

Hi @lburmz ,

Actually ISO format will be used for Text type Date&Time values from Excel. If you would like to get the whole date and time saved into Excel, you need to change one of the two parameters of the div function to float point number to make the result be float point number as well, otherwise you will get a integer that represents date only. So, change 864000000000 to 864000000000.0 will solve the issue.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
lburmz 28 on at

Like (0)

Report

That is exactly what I need. Thank you very much.

Was this reply helpful? Yes No