Formatting Time

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
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
 

Some videos you may like

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
Joined
May 9, 2009
Messages
16,623
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
May 26, 2010
Messages
320
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

OK, In 2003 try this formula

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

format as before
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,803
Messages
5,524,970
Members
409,612
Latest member
Jagadeeswaran Stalin

This Week's Hot Topics

Top