Forum Home
Press F1
Thread ID: 70866	2006-07-19 03:45:00	excel query	jonp (7517)	Press F1

Post ID	Timestamp	Content	User
471953	2006-07-19 17:37:00	Hi, Im a bit late reading this thread but the way Excel cells are formatted can significantly change the way the results are displayed for dates/times. As an example, if you type the 9:00 in cells A1:A5 and 15:00 in cells B1:B5 then in column C have the formula =B1-A1 copied down you will see that the result is 6:00 for these 5 results. So far so good. You will see the format by selecting Format|Cells is H:MM. Now sum column C by =SUM(C1:C5) in C6 and you will see the answer is .... 6:00. Weird you may rightly say, the answer should be 30:00. To understand why this is displaying 6:00 in column D enter the formula =C1 in D1 then drag formula down to D6. Now in D1 to D6 select Format|Cells and select the Number category option with 2 decimal places. You will see D1 to D5 is showing 0.25 and D6 1.25. This is how Excel stores dates/times - as a number. The time of 6 hours is a 1/4 of a day hence 0.25. The integer (ie 0) is 0 because you havent specified a date but this is irrelevant to the problem. Because C6 is formatted as H:MM then only the hour and minutes are displayed, not the days hence this leaves only the .25 bit rather than 1.25 so you will see only 6:00 displayed as the sum of C1:C5. To avoid this issue simply change the format of the cell that contains the sum formula to this... [H]:MM. This will force the format to sum as hours and minutes and will display 30:00 as the result. If you would prefer to display in words you can use a format like this... d "days," h "hours and " mm "minutes" Hope this gives you a bit of insight into how Excel stores and displays dates and times. regards, Graham	Parry (5696)
1 2