How to format fields in CSVDE csv dump to display as a date?

[Cappy]

Archived from groups: microsoft.public.adsi.general,microsoft.public.win2000.active_directory (More info?)

Hello!

I have run a CSVDE dump of all of the "users" in our Windows2000 domain. One of
the items we are interested in tracking is the lastLogon. When I import the
output of the dump in to Excel, the values in the lastLogon column are displayed
in this format:

1.27652E+17


The cell type in the spreadsheet is "General."

If I change the format of the cells in the column to "Number," I get this:

127651540246987000


(This is also the string that is in the output file.)

This is not a UTC date, this particular string of digits translates to Dec 27,
2007. Although, I don't think my little UTC translator understands all of those
digits. 1270000000 translates to Mar 30 2010. grin

If I format the cell to date, I get a string of # signs.

The whenChanged and whenCreated fields come in as recognizable dates, although
they do have a .0Z "extension."

Any help formatting the CSVDE dump would be greatly appreciated.


Cappy

 

[Guest]

Archived from groups: microsoft.public.adsi.general,microsoft.public.win2000.active_directory (More info?)

Try http://www.rlmueller.net/Last%20Logon.htm



--


Paul Bergson MCT, MCSE, MCSA, CNE, CNA, CCA

This posting is provided "AS IS" with no warranties, and confers no rights.


"Cappy" <cappy_p@juno.com> wrote in message
news:e0SijSIuFHA.1544@TK2MSFTNGP10.phx.gbl...
> Hello!
>
> I have run a CSVDE dump of all of the "users" in our Windows2000 domain.
> One of
> the items we are interested in tracking is the lastLogon. When I import
> the
> output of the dump in to Excel, the values in the lastLogon column are
> displayed
> in this format:
>
> 1.27652E+17
>
>
> The cell type in the spreadsheet is "General."
>
> If I change the format of the cells in the column to "Number," I get this:
>
> 127651540246987000
>
>
> (This is also the string that is in the output file.)
>
> This is not a UTC date, this particular string of digits translates to Dec
> 27,
> 2007. Although, I don't think my little UTC translator understands all of
> those
> digits. 1270000000 translates to Mar 30 2010. grin
>
> If I format the cell to date, I get a string of # signs.
>
> The whenChanged and whenCreated fields come in as recognizable dates,
> although
> they do have a .0Z "extension."
>
> Any help formatting the CSVDE dump would be greatly appreciated.
>
>
> Cappy
>
>

 

[Guest]

Archived from groups: microsoft.public.adsi.general,microsoft.public.win2000.active_directory (More info?)

Might also suggest www.joeware.net Possibly because I always forget to
suggest it, but also because there would be no script writing/customization.
It's an .exe vs. a script. Either would be helpful.

You *could* likely take some of that information and use it to convert the
output in the Excel file, but why take the extra step?

Al

"Paul Bergson" <pbergson@allete_nospam.com> wrote in message
news:uafUK$JuFHA.3528@TK2MSFTNGP15.phx.gbl...
> Try http://www.rlmueller.net/Last%20Logon.htm
>
>
>
> --
>
>
> Paul Bergson MCT, MCSE, MCSA, CNE, CNA, CCA
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> "Cappy" <cappy_p@juno.com> wrote in message
> news:e0SijSIuFHA.1544@TK2MSFTNGP10.phx.gbl...
>> Hello!
>>
>> I have run a CSVDE dump of all of the "users" in our Windows2000 domain.
>> One of
>> the items we are interested in tracking is the lastLogon. When I import
>> the
>> output of the dump in to Excel, the values in the lastLogon column are
>> displayed
>> in this format:
>>
>> 1.27652E+17
>>
>>
>> The cell type in the spreadsheet is "General."
>>
>> If I change the format of the cells in the column to "Number," I get
>> this:
>>
>> 127651540246987000
>>
>>
>> (This is also the string that is in the output file.)
>>
>> This is not a UTC date, this particular string of digits translates to
>> Dec 27,
>> 2007. Although, I don't think my little UTC translator understands all
>> of those
>> digits. 1270000000 translates to Mar 30 2010. grin
>>
>> If I format the cell to date, I get a string of # signs.
>>
>> The whenChanged and whenCreated fields come in as recognizable dates,
>> although
>> they do have a .0Z "extension."
>>
>> Any help formatting the CSVDE dump would be greatly appreciated.
>>
>>
>> Cappy
>>
>>
>
>

 

[ElizabethGreene]

Hi. Sorry to ressurect a moldy-oldie thread, but this is the first thing google sees for this topic.

The formula to convert from Active Directory LastLogon or LastLogonTimestamp for excel is:
[cpp]
=IF(C2>0,C2/(8.64*10^11) - 109205,"")[/cpp]



Explanation:
* C2 is the cell that contains the Timestamp.
* The If() statement hides the value if the user has not logged in.
* (8.64*10^11) is the number of nanoseconds in a day divided by 100.
* 109205 is the number of days, including leap days, between 1601 and 1900. (1900 is when excel dates "start")

That's it. Paste in your formula and format it as a date, or date/time. The times returned are in GMT.

HTH,
Elizabeth Greene