Novell is now a part of Micro Focus

Reporting on Last Scan Date

Novell Cool Solutions: Trench
By Tracy Van Slyke

Digg This - Slashdot This

Posted: 16 Jan 2002

Current version: ZENworks for Desktops 3.2

I have been using database reporting solutions to report on ZENworks inventory and one of my problems is how to get something useful out of the last scan date variable.

I don't know if this is worth posting on your site or not but I spent a little time coming up with the conversions, so I thought this might help others trying to report on last scan date.

This conversion is good for Excel, Access and Crystal Reports.

This is the way I approached it and I made several assumptions.

Excel Format:

Last scan date is the number of milliseconds since Jan 1, 1970. Excel's date/time format is the number of days since Jan 1, 1900.

To get Excel to display the correct date, just divide the last scan date by 86400000 and add 25569. I am assuming this gives you the GMT time. Then just subtract or add the amount of time for your time zone. For mountain time it is 7 hrs, which in days calculated to .29167. Then use Excel's date format to display the date/time.

The formula for mountain time is:

=LSD / 86400000+25569-0.29167
Where LSD is the cell containing the last scan date value.

MS Access:

CVDate(Val([Scanner Information_Last Scan Date])/86400000+25569-0.29167)

Crystal Reports Format:

I created a formula field to manipulate the value.
(This was a formula from a report using the NDS ODBC driver, thus the Workstation.ZEN:INV Last Scan Date)

if numerictext({Workstation.ZEN:INV Last Scan Date}) then ToText (DateTimeValue (ToNumber ({Workstation.ZEN:INV Last Scan Date})/86400000+25569-0.29167))
else trim({Workstation.ZEN:INV Last Scan Date})

If you have any questions you may contact Tracy at

Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions.

© Copyright Micro Focus or one of its affiliates