Flow running correctly but date fields time is not matching

[breid]

Hello all,

I am creating a flow to take excel data (in a table) and import to existing custom SP list.  I finally got it running correctly, but the dates are not getting the right time stamp.

Excel Data

SP List Data

The timestamp is off ... 

From extensive googling I have found this formula to get the proper timestamp into Sharepoint

addSeconds('1899-12-30',int(formatNumber(mul(float(variables('StrTimeIn')),86400),'0')))

It works but you can see the corresponding rows of data, the SP List date/time stamp is off..

Can anyone help?

Flow:

[Pstork1]

The formula you found is to translate from the serial date provided by Excel to a date time stamp.  The reason your times are off is because Excel is time zone independent and SharePoint stores time in Universal Time Code and then displays it in the local time zone of the user.  There is also another factor that needs to be taken into account when you update a date and time the way you are.  SharePoint will try to convert it to the time zone of the SharePoint data center before storing it internally in UTC.  So for example, if you store a date/time from Excel directly to SharePoint it will assume the date and time is in your local time zone and convert it to the Server's time zone.   That is why you are off.  You can find the time zone for your SharePoint site by looking in the Regional Settings under Site Settings. You need to allow for that offset when you transfer times from Excel to SharePoint.

[breid]

@Pstork1 - I have verified my Sharepoint timezone data, and it is correct.  The data is in fact stored as the excel serial number.. with a decimal portion for the time. In a sharepoint online folder.  

Time in is General format, and Time Out is excel custom 'mm/dd/yyyy hh:mm' for ease of viewing ..

All these times are in morning, and the SP list is showing a random set of times ....

First row of SP list data corresponds to line 16 in Excel data. How does 9:29PM relate to 6:06AM ?

How would I account for the offsets?..

Bruce

[breid]

Is there a better way?  I'm not married to this method, just was the best way I could get it to work.

Excel file in Sharepoint folder, need to ETL into a Sharepoint  List automatically..

[Pstork1]

If it was the timezone issue it would be off by hours, not hours and minutes.  Are you using the TimeIn and TimeOut columns in the SharePoint site?  Or only one.  I see two time columns in your sp list.  and one of them is for 10:06 PM.  That's more the number I would expect if its a time zone issue.

Otherwise, I suggest adding a compose for each of the SharePoint columns you are using so you can see the actual date time stamp that is being submitted to SharePoint.  If those aren't right then you need to take another look at the formula you are using to translate form the serial date in Excel to Date Time.

[Pstork1]

I can't say there is a better approach.  But use Compose statements at each step to make sure you are getting the values you should be and double check all the formulas.  It should work.

[breid]

Here is the output of the lines, in this view it is correct.

Here is the raw output.

I assume it is the "Z" that is causing the issue.  I have confirmed that the minutes are not changing...

I need to wrap the formula into a 

Correct?

Bruce

[Pstork1]

The Z at the end simply means the time is in the UTC timezone.  But there has to be something wrong with the conversion formula or something.  YOur one screenshot shows Time in as 6:09 and the other as 5:17.  So I don't understand the comment that the minutes aren't changing.  That's not a change in timezone.  That's something wrong in converting the time itself.

What is the raw input for Time_in coming from Excel? 

[breid]

I was mistaken... I was looking at a different line of data .

Excel online as custom (m/dd/yyyy hh:mm AM/PM)

Excel online as General 

The minutes are the same input to Sharepoint rows.

[Pstork1 · Accepted solution]

OK, lets take this a step at a time for one of the fields, Time In.

1. In Excel its 5:29 AM (remember Excel is timezone independent)
2. In SharePoint the input is 5:29 AM (Note the lack of a Z at the end.  This is also timezone independent)
3. In SharePoint the output is 5:29 AM UTC (That's what the Z at the end means.  So its no longer timezone independent it is UTC time. )

So the SharePoint Time In field is set for 5:29 AM UTC.  But when its didsplayed that is automatically adjusted by the time zone of the SharePoint server.  So its no longer 5:29 AM.  Its now the equivalent of 5:29 AM UTC in the server's timezone (or your local timezone depending on settings.)

so before putting it into SharePoint as UTC you need to shift the timezone independent values to compensate for SharePoint using UTC for input.

[breid]

@Pstork1 - I was able to get it to work successfully.  In the flow below, I inserted a convert timezone for each variable and ended up setting both to CST, and the timestamp came through correctly.  Strange ..