You’re offline. This is a read only version of the page.
I'm hoping somebody can shed some light on this issue. I am trying to build a flow that adds new entries/updates existing entries in a SP list based on an Excel Spreadsheet stored on OneDrive. The spreadsheet contains both columns with dates and date & time. I've researched this extensively, trying to get the dates properly transferred into SP. The common solution is to use an expression AddDays (for dates) and AddSeconds (for time), as per tutorials here and here.
However, these methods require the dates and time to be passed as numerical values (integers and float respectively) from Excel. It appears that the Excel Online connector only passes those values as string. As discussed in this thread, this used to work with the standard Excel connector, but this connector got deprecated. I'm hoping somebody has a workaround or solution. Thank you!
I'm not sure what causes the 7 hours discrepancy, but got it to work by adding the time. This is the final expression:
addSeconds('1899-12-30',add(int(formatNumber(mul(variables('StartTimeFloat'),86400),'0','en-us')),25200),'yyyy-MM-dd H:mm')
Thank you everyone!
For sure, @efialttes , please see below:
"Start Date":"44098","Start Time":"0.375","End Time": "0.625" --> come from Excel as string
Then, I used int and float expressions in Power Automate, as suggested, to convert the values to integers and float.
Since SP expects date & time in the time columns, I added the date value back to the float values for time, getting the following outputs for time:
Start Time: 44098.375
End Time: 44098.625
These are the values that are passed to this expression:
addSeconds('1899-12-30',int(formatNumber(mul(variables('StartTimeFloat'),86400),'0','en-us')),'yyyy-MM-dd H:mm') --> returning in the SP list for start time 9/24/2020 2:00 AM (instead of the expected 9/24/2020 9:00 AM)
addSeconds('1899-12-30',int(formatNumber(mul(variables('StartTimeFloat'),86400),'0','en-us')),'yyyy-MM-dd H:mm') --> returning in the SP list for end time 9/24/2020 8:00 AM (instead of the expected 9/24/2020 3:00 PM)
Thank you so much for looking into this.
Hi again
In order to replicate the scenario, can you share the time raw values provided by your Excel action block? I mean, the 'List rows present in a table' column outputs before applying WDL expressions
Thanx!
Thank you for @efialttes
Hi!
You should be able to convert a number in string format to int or float by means of int() or float() WDL expressions
If you need further assistance please let us know
It appears that the Excel Online connector only passes those values as string. So if your Excel column is of tipe Date (no Time) you can try with the following
Share
Report
All responses (
Answers (
addDayaddDays('1899–12–30T00:00:00Z',int(item()?['TestingDate']))
Just replace item()?['TestingDate'] with the coresponding dynamic content that represents your date column.
Also this excelent article discusses DateTime translation
Another approach is, some people prefer to switch their Excel column format from date into text
Hope this helps
#1
WarrenBelz
146,769
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,091
Most Valuable Professional
