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):
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.
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With