Formatting Time

rhombus4

Active Member
Currently add up times which then calcualte a formula, however I want to also show the total time as Hours and minutes. each time is hours and minutes, however below it will show as 4.8 which is 4hours 80 mins how do i get it to show as 5 hrs 20 minse.g. have times as 1.1 1.1 1.25 1.35

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

T. Valko

Well-known Member
Currently add up times which then calcualte a formula, however I want to also show the total time as Hours and minutes. each time is hours and minutes, however below it will show as 4.8 which is 4hours 80 mins how do i get it to show as 5 hrs 20 minse.g. have times as 1.1 1.1 1.25 1.35
Why don't you just "go with the flow" and enter the times as TIMES?

It will be much easier!

barry houdini

MrExcel MVP
Which version of Excel are you using? In Excel 2007 or later versions you can use this formula, assuming data in A2:A10

=SUMPRODUCT(DOLLARDE(A2:A10+0,60)+0)/24

format result cell as [h]:mm

rhombus4

Active Member
using excel 2003, reason havent entered as times because alot of data from old spreadheets which is linked which also calcluates some days and other stuff

barry houdini

MrExcel MVP

OK, In 2003 try this formula

=SUMPRODUCT(A2:A10+MOD(A2:A10,1)*2/3)/24

format as before

rhombus4

Active Member
OK, In 2003 try this formula

=SUMPRODUCT(A2:A10+MOD(A2:A10,1)*2/3)/24

format as before

Thanks, above worked great

rhombus4

Active Member
OK, In 2003 try this formula

=SUMPRODUCT(A2:A10+MOD(A2:A10,1)*2/3)/24

format as before

1 quick thing, If I have a list which have been formatted already i.e. showing 5:20 how do I format it back so that it shows the old way for my previous calculations i.e.4.8

Replies
18
Views
278
Replies
4
Views
81
Replies
7
Views
109
Replies
4
Views
70
Replies
8
Views
61