cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
breid
Helper I
Helper I

Flow running correctly but date fields time is not matching

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

Excel Data 1.png

 
 

SP List Data

SP List Data 1.png

 

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:

Flow part 1.png

 

 

Flow part 1a.png

 

Flow part 2.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

10 REPLIES 10
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@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 ....

 

Excel Data 2.png

 

SP List Data 1.png

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

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
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

Evaluated output to list.png

 

Here is the raw output.

JSON output- showing Z in dateTimeStamp.png

 

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 

Wrap functions to get proper timezone.png

 

Correct?

 

Bruce

 

Pstork1
Dual Super User III
Dual Super User III

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? 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

 

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

excel - formatted as custom.png

Excel online as General 

excel - formatted as general.png

 

The minutes are the same input to Sharepoint rows.

Raw Inputs.png

Raw Outputs.png

Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

@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 ..

Good process.png

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (85,971)