Need help: convert seconds to date and time

[misty1]

Need help converting cell contents from seconds to a date and time. File was exported from Active Directory into a CSV file. Other date fields display date and time, i.e. 20041029043416.0Z. All of the cells in the column for last logon displays an exponential number i.e. 1.27771E+17. When this cell is accessed with mouse or when copied and pasted, 127770982728624000 is displayed. After searching the Internet on converting seconds to date, I found this formula but am not sure how to use it on the cells in the column for last logon.
Set cell format to:
Date, type = y/m/yy hh:mm
Set cell function to:
=unixdatetime /(60*60*24)+DATE(1970,1,1)
This will retun date and time

I would appreciate a step by step process on how to convert seconds to date and time using the example: 127770982728624000.
Thanks in advance for your assistance.

[Earl Kiosterud]

Misty,

127770982728624000 seems too big a number for a Unix date-time value.
Today's date is 1132876800. It's the count of seconds since 1/1/1970,
right?

Earl Kiosterud
www.smokeylake.com

"misty1" <[email protected]> wrote in
message news:[email protected]...
>
> Need help converting cell contents from seconds to a date and time.
> File was exported from Active Directory into a CSV file. Other date
> fields display date and time, i.e. 20041029043416.0Z. All of the cells
> in the column for last logon displays an exponential number i.e.
> 1.27771E+17. When this cell is accessed with mouse or when copied and
> pasted, 127770982728624000 is displayed. After searching the Internet
> on converting seconds to date, I found this formula but am not sure how
> to use it on the cells in the column for last logon.
> Set cell format to:
> Date, type = y/m/yy hh:mm
> Set cell function to:
> =unixdatetime /(60*60*24)+DATE(1970,1,1)
> This will retun date and time
>
> I would appreciate a step by step process on how to convert seconds to
> date and time using the example: 127770982728624000.
> Thanks in advance for your assistance.
>
>
> --
> misty1
> ------------------------------------------------------------------------
> misty1's Profile:
> http://www.excelforum.com/member.php...o&userid=29112
> View this thread: http://www.excelforum.com/showthread...hreadid=488299
>

[misty1]

Hi, I tried to reply to your reply regarding the conversion of seconds to date and time and although I had signed in, I could not get the screen where I could reply to your questions so I found your e-mail address at www.smokeylake.com

I am not sure what the number means except that it is supposed to be a date and time that occurred in November, 2005 and the cell contents are under a field called lastlogon. I am trying to convert this to a date and time so that it can be easily determined when a person last logged into his/her account in AD. The file was exported from Active Directory into a CSV file. This number still shows the same in both the text file (in Notepad) and the csv file (in Excel).