Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

time

excel

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 Cell with data

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): Excel 2010 - Time Format Cell

like image 288
PinkSheep Avatar asked Apr 24 '13 16:04

PinkSheep


People also ask

How do you extract hours from HH mm SS in Excel?

Select a blank cell, and type this formula =TIME(HOUR(A1),MINUTE(A1), SECOND(A1)) (A1 is the first cell of the list you want to extract time from), press Enter button and drag the fill handle to fill range.

How do I display only hours and minutes in Excel?

A keyboard shortcut for doing so is Ctrl-1. On the Number tab choose Custom. Scroll down the list of custom formats and choose h:mm, and then click OK. A shortcut for closing the Format Cells dialog box is to double-click on h:mm.


1 Answers

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)

like image 170
barry houdini Avatar answered Sep 18 '22 08:09

barry houdini