Solved

integer8 values

Posted on 2016-11-25
1
26 Views
Last Modified: 2016-11-25
I have ran an AD cmdlet report which gives a lot of attributes about users, such as last logon dates. It output the results to a csv file, which i am trying to analyse via excel 2010. From what I have read, the output of what should be dates, is sometimes "Integer8 (64-bit numbers representing dates in UTC)".

e.g.
131054662674668000
130816106481779000

I need a formula to compare these into dates (UK date format; dd/mm/yyyy) I can use and therefore analyse properly, as at present they are pretty useless.
0
Comment
Question by:pma111
1 Comment
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 41901635
In your Excel file, have a helper column with the formula:

=(((ROUNDDOWN(A1/10000000,0)/3600)+0)/24)-109205

Open in new window


Obviously, now this whole column is still unreadable (it's like 1420200000), but it will become readable after you select the column, and format it  it to Date with the  date format you need.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question