You’re offline. This is a read only version of the page.
24
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.
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.
Hi @lburmz ,
You will need serial numbers to save to Excel datetime column:
Share
Report
All responses (
Answers (
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.
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
