How to transform string to “d-m-y (h)h:mm” format in Excel?

Posted on

QUESTION :

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?

ANSWER :

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.

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)

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.