Hi, I have a flow that is connected to excel and uses the values to populate a sharepoint list. It is returning the date value as a number. I have read other post about this issue and input the following formula in the date values for the create item part of the flow:
There are two ways to make it possible.
Option A. Change the type of the column which store the date to "string" format.
(You need to keep the input contents of date format the same way as well.)
1. Copy the area which you would like to change from date to string.
2. Paste the copied cell to a text editor. The cell data will be shown as text format separated by tab.
3. From a text editor, select all and make a copy.
（Ctrl + A ⇒ Ctrl + C）
4. Go back to Excel, and change the original cell's format to sting by set dialog.
The original date will be shown as number, no need to worry about it.
When you overwrite the original cells by pasting the text data (Ctrl + V), the date will be entered as string in the cell.
Option B. With the text function, the date should be converted to a string format and store it in another cell,
and retrieves the value of the converted cell on the flow.
1. Add a column to the Excel table to store string dates. In the cell value, type as follows.
"=TEXT([Date format to be retrieved],[Format you want to retrieve])"
e.g. =TEXT(I2,"yyyy/mm/dd hh:mm:ss")
2. By flow, the created column in 1 will be set in SharePoint date columns with dynamic contents of Excel.
I understand this is already late but might help others looking for solution to same problem. you can try with this formula:
Thank you @ShriramP
Our flows stopped working correctly yesterday. Your post helped get them running again.
formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['TermDate']),-2)), 'MM/dd/yyyy')
Works as expected.
Check out the News & Announcements to learn more.
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.