Excel - Converting Unix date to mmddyyyy:hr:min:sec format within Excel

Asked By PiyushA on 12-Jul-07 06:26 AM
I need to convert a column full of numbers representing date in Unix format
to the Windows date mmddyyyy:hh:mm:ss format.
Can anyone provide me a macro which can do this for a column full of data?
A sample of the data (in Unix Date format) is listed below:
1181846741
1181930850
1182017262
1182096202
1182182688
1182269074
1182355470
1182441813
1182528209
1182614575
1182731309
1182787489
1182873806
1182960243
1183046665
1183133077
1183219376
1183305815
1183392260
1183478655
1183565047
1183651427
1183737883
1183824182
1183910610
1183997155
1184083549
1184169943
1181846625
1181846741

Thanks in advance for your help
regards
PiyushAg




Mike replied on 12-Jul-07 06:36 AM
Assuming your unix times are in a1 down put this in B1 and drag down

=A1 / 86400 + 25569

Format as date

Mike
Niek Otten replied on 12-Jul-07 06:38 AM
See

http://www.mcgimpsey.com/excel/udfs/unixtoxltime.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
David Biddulph replied on 12-Jul-07 06:44 AM
Not a macro, but
http://exceltips.vitalnews.com/Pages/T0096_Converting_UNIX_DateTime_Stamps.html
--
David Biddulph
Mike replied on 12-Jul-07 06:52 AM
Sorry I should have added youcan format up to this level of precision:-


dd/mm/yyyy hh:mm:ss

Mike
PiyushA replied on 12-Jul-07 12:44 PM
Worked like a charm - a 10
Thanks Mike
Regards
PiyushAg