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.

I want to transform a bunch of string cells into date (and time) cells, in order to calculate the difference between the current date and time, and the initial date.

Currently the string cells look like this: "6-17-2013 1:35"

But this needs to be transformed to the d-m-y (h)h:mm format.

Now, how to do this?

share|improve this question

migrated from stackoverflow.com Jun 18 '13 at 18:56

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 2 down vote accepted

have you tried =DATEVALUE()+TIMEVALUE(), which transforms a string to a date type. And use cell formatting to get the requested number formatting.

Or even TEXT(DATEVALUE() + TIMEVALUE(), "m-d-yyyy h:mm") to directly format it back to a string, but now with the correct looks.

share|improve this answer
1  
This works, but only for day, month and year. Hours and minutes are set automatically to 00:00 (or 12 AM). What am I missing here? –  daniel_ev Jun 18 '13 at 13:40
    
indeed, I fixed for that by adding TIMEVALUE() –  K_B Jun 18 '13 at 13:42
    
That did the trick. Thanks! –  daniel_ev Jun 18 '13 at 13:56
    
you are most welcome, and good luck! –  K_B Jun 18 '13 at 13:57

You can just select all the cells in which the values are contained then format the cells, editing the type to show the format you need. For example: [$-409]d/m/yyyy (h)h:mm

(You can use yy instead of yyyy to show 13 instead of 2013)

Format Cells Menu

share|improve this answer

Right click on the cell/Column Select Format Cells. In the window that pops up, Select Custom. In the Text Box under the word Type: then replace the word General With

d-m-y (h)h:mm 

Not sure what the (h) is for though, In excel it just going to repeat the hour portion of the time inside of the parentheses. there is a [h] format option that displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, but That should never happen when working with hours in a day.

share|improve this answer

Your Answer

 
discard

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