Excel 2010: Hours and Minutes extract from hh:mm:ss formatted cell

Question

For example:

I have a cell with the following value:

"1/11/1900 4:00:20 AM"

that will be displayed inside the cell as: "268:00:20" - meaning 268 hours, 00 minutes and 20 seconds (see the first image below).

EDIT: Here is screen of how it looks like and the formatting of the cell

Here comes the Question:

What is the Excel 2010 formula (no VBA if possible!) to extract hours and to extract minutes from this cell, such as "268" should be shown for hours in a different cell and "00" (in this case) for minutes in another cell?

For clarification: the "268:00:20" is a duration of 268 hours and 00 minutes and 20 seconds.

What I'd like to get? In different cells, store the hours (268) and minutes (0)

The cell is formatted as Time (see the image below):

Answer 1

Just use Text To Column function under Data in the Menu Bar. Works really well for time that is more than 24 hours. I had a problem where the minutes and seconds were displayed inconsistently sometimes in 2 digits and sometimes in 1 digit eg. 268:1:13 or 541:12:3 or 671:45:30. In that case just select delimiter as ":" (vs Fixed Width option) and it will be able to display the Hours Minutes and Seconds in separate columns. Problem Solved!

Answer 2

The value 1/11/1900 4:00:20 AM is really only a formatted number in Excel representing the number of days, i.e approx 11.167 in this case - I think Daniel's solution is not far off - using DAY and HOUR will work for values up to 31 days.......but for a more generic solution for hours use just:

=INT(A1*24)

and for minutes

=MINUTE(A1)

Answer 3

You will have to use Day and Hour formulas. The Hours will return only the time part. You will have to sum it with 24*Day. Maybe you will need to use Year as well, depending on what you want.

For the minutes, use Minuteformula.

If there's a start date and the desired date, you can use 24 * Days(DesiredDate, StartDate).

Answer 4

You do not need any formulas at all. Just format the cell with this format [h]:mm:ss;@

Example below:

Say your date is in Cell A1 as shown:

Go to format options:

Then Under the Number Tab Select Custom. Then enter the above Custom time format into the box:

And your cell will end up as:

Or by formula you could do it using DateDif() with the formula =DATEDIF(0,A1,"D")*24+HOUR(A1)&":"&MINUTE(A1)&":"&SECOND(A1)

as shown below:

Answer 5

I would use something similar to what I used here. It's not really pretty for a solution but it works.

So, in your case what you do is:

1. Format the values in the time you want to, and copy/paste the value (or column) into notepad. The clipboard copies and pastes what's visible instead of the date/time value in excel.
2. In your worksheet, insert a new column and format it as text. This will cause excel to accept the values as is and not convert them into date time.
3. Copy everything from notepad and paste into the new column you just formatted as text. You will get the values of the time as is in excel.

From there, you can use a Text to column with colon delimited to split the time to get 3 columns: hours, minutes and seconds (and additional tip, in the split to column window, where you are asked about the format of the split columns, format all as text).

EDIT: Since it doesn't seem too obvious...

• Column B is the original date/time.
• Column C is the same date/time formatted in hours, minutes and seconds only. Copy and paste column C in notepad.
• Format Column D as text, then copy the values in notepad to the column. This gives you the time stored as text.
• Column E to G is the split of column D, colon delimited.