Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How would I convert a format like this in Excel:

01.06.2007 21:48:16

To this:

Sat Jan 06 21:48:16 EDT 2007?

Assume these are US dates, so 01.06.2007 is actually Sat Jan 06 2007 and all my source cells are EDT.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

There aren't really any good date conversion functions in Excel, like there are in SQL, so you have to use some MID() selections.

Cell A1:

01.06.2007 21:48:16

Convert the date to ISO format and convert it to an Excel date serial, convert the time to an Excel time serial then add them together:

Cell A2:

=DATEVALUE(MID(A1,7,4)&"-"&MID(A1,1,2)&"-"&MID(A1,4,2))+TIMEVALUE(MID(A1,12,8))

Now you need to format the cell to get the date format you want: ddd mmm dd hh:mm:ss "EDT" yyyy

If you need to offset the original date and time, then add or subtract from the formula in A2; this example will add 2 hours to the original:

=DATEVALUE(MID(A1,7,4)&"-"&MID(A1,1,2)&"-"&MID(A1,4,2))+TIMEVALUE(MID(A1,12,8))+TIMEVALUE("2:00:00")

Result:

Sat Jan 06 23:48:16 EDT 2007
share|improve this answer
    
That was so helpful and so quick! Many thanks, I really appreciate your help! –  Alex Barash Sep 6 '11 at 4:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.