On Tue, Dec 15, 2015 at 6:01 PM, Scooter C <scoo...@scootersdesk.com> wrote:
> I've been trying, in vain, to construct a formula for a time value from > enter times. > I want the minutes or hours from a beginning clock time to an ending clock > time. > The formula in the help screen: =(c2<b2)+ c2-b2. I tried it but it wasn't > right. c2 is the end time, b2 is the start time > This formula doesn't give what I want, which is how many hours were > involved? > If the c2 was 11:00 AM & b2 was 9:00 AM the answer should be 2 Hours, but > that's not the "answer" I get. > I get a time, also not correct. > Hello Scotter. You say you get a "time". If my guess is correct you get 02:00:00 AM or similar? That indicates to me the answer is correct, it is just the format of the cell that is not correct. Try selecting the formula cell, right-click and select Format, make certain the Category is time and the selected Format is 13:37 (the actual mask is HH:MM). The displayed value should change to 02:00, 2 hours. To display the result in a different format you will need to experiment with the format applied to the formula cell. To display time differences across days the format needs to change to [HH]:MM and the date must also be entered along with time in each cell (even if it is not displayed). In my experiments I entered your values into B2 and C2 and your formula into D2. After applying the 13:37 (HH:MM) format the time value displayed as 02:00 If 9:00 AM is entered in B2 and 12/16/2015 11:00 AM (I'm writing this on 12/15) is entered into C2 the time value displays as 02:00. Change the format of C2 to [HH]:MM and the value displayed becomes 1016498:00. ? Yup. Now change the value in B2 from 9:00 AM to 12/15 9:00 AM and the display in C2 becomes 26:00. What's happening? Just like bold and italic changes the way text looks, number formats change the way numbers are displayed. To show hours greater than 24 and minutes the format [HH]:MM needs to be applied to the cell. Once [HH]:MM format is applied the result of the date math becomes apparent. If a time is entered with no date AOO presumes the time is on the zero date used as a reference. That zero date is 12/30/1899, 116 years ago, 42,354 days ago, 1,016,498 hours ago. So to display hours and minutes between two times on different days it is necessary to enter the date as well as the time into the cell and format the result as [HH]:MM. If the requirement is to show hours and minutes between two times on the clock that are within 24 hours of each other then no need to include the date as part of the value entered into the cell.
