Excel 2010: Hours and Minutes extract from hh:mm:ss formatted cell
excel, time
Solution
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)`
Problem
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):