![]() It is possible to use the same approach with the Text function (Row 9 below).Time differences greater than 24 hours should now display correctly in hours and minutes (see cell F8 below).Edit in the Type box by putting square brackets around the h: “:mm”.On the Home tab, select the Number Format dialogue.The default time format has the same problem as the “h:mm” format string, so 36 hours displays as 12, but it is possible to display the correct number of hours: This can be changed to display days, hours and minutes with the format string “d:h:mm”.Īnother approach is to leave the time difference as a date number and format to display as hours. This will work correctly for time differences up to 24 hours, but the display resets to zero after each 24hours, so the 36 hour time difference above displays as 12 hours. Note that the format string overrides the cell format, so the returned text displays the same regardless of the cell number format. Microsoft recommends use of the Text function, with the format string “h:mm”. This shows the correct result if formatted as a decimal, but if is formatted as time it shows the wrong value. The first row shows the result of multiplying the date difference by 24. The screen-shot shows various alternatives for displaying the number of hours between the date/time in cell B3 and the start of the date in B2. If we want the time in hours and minutes (or hours minutes and seconds), things get a bit more complicated: To calculate the number of hours between two dates we can simply subtract the two values and multiply by 24. Dates and times in Excel are stored as a date number, which is the number of days since 1 January 1900, with the value after the decimal point representing the time of day.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |