Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
misty1
 
Posts: n/a
Default Need help: convert seconds to date and time


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Earl Kiosterud
 
Posts: n/a
Default Need help: convert seconds to date and time

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" wrote in
message ...

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
misty1
 
Posts: n/a
Default Need help: convert seconds to date and time


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


--
misty1
------------------------------------------------------------------------
misty1's Profile: http://www.excelforum.com/member.php...o&userid=29112
View this thread: http://www.excelforum.com/showthread...hreadid=488299

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VLOOKUP with a Date and Time Charles Excel Discussion (Misc queries) 4 September 20th 05 06:38 PM
How to convert DATE and TIME to text ice_pack Excel Worksheet Functions 3 August 15th 05 10:30 AM
convert interval to various separate date , time, hr, minutes Todd F. Excel Worksheet Functions 4 July 12th 05 07:25 PM
Convert date to length of time in months from set date MJUK Excel Worksheet Functions 1 March 19th 05 07:31 PM
how do I convert a date and time column to a time column thdorsky2 Excel Worksheet Functions 1 March 4th 05 09:49 PM


All times are GMT +1. The time now is 07:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"