Kitz ADSL Broadband Information
adsl spacer  
Support this site
Home Broadband ISPs Tech Routers Wiki Forum
 
     
   Compare ISP   Rate your ISP
   Glossary   Glossary
 
Please login or register.

Login with username, password and session length
Advanced search  

News:

Author Topic: Exel formulae required  (Read 3722 times)

oldfogy

  • Helpful
  • Kitizen
  • *
  • Posts: 3568
  • If it ain't broke....... I'll soon fix it.
Exel formulae required
« on: February 28, 2013, 04:16:00 PM »

Hopefully a easy one for someone to help me, I need a Exel formulae for adding up time.
Adding up as a decimal is not a problem, but I need to be able to add up using Hours and Minutes as the column can be fairly long and so far no matter how much I try I just can't get it right.

What I need to add up is a column of hours and minutes.
The actual words of hrs & mins are just for illustration.
IE:

  2hrs 15mins
  3hrs 30mins
  4hrs 10mins
  2hrs 30mins
12hrs 25mins
Logged

geep

  • Reg Member
  • ***
  • Posts: 452
    • My ST546 Statistics
Re: Exel formulae required
« Reply #1 on: February 28, 2013, 06:10:11 PM »

Just format the cells with Custom format [hh]:mm
If you can't see that format, select hh:mm and modify it in the box marked Type: and press OK. This creates the new format.
Cheers,
Peter

Edited - added further details and revised screenshot.
« Last Edit: February 28, 2013, 06:24:01 PM by geep »
Logged

oldfogy

  • Helpful
  • Kitizen
  • *
  • Posts: 3568
  • If it ain't broke....... I'll soon fix it.
Re: Exel formulae required
« Reply #2 on: February 28, 2013, 06:20:47 PM »

Thanks for that, although I have already tried just the HH:MM format but without any use, I'll try the DD:HH:MM but I could really do with it being only hours and not days as at a later part the total has to be divided by a figure of (at the moment 3).

Basically it will be the motoring mileage between multiple points across Europe (A to B, B to C, etc) of which at the moment the total initially will be about 10 different points spread over 1,800+ miles which is to be travelled over 3 or 4 days.
Basically it's easy to copy the figures over from any of the maps being used and quicker than typing them but the formulae is needed to add them all back together.


Warning - while you were typing a new reply has been posted. You may wish to review your post.
Logged

geep

  • Reg Member
  • ***
  • Posts: 452
    • My ST546 Statistics
Re: Exel formulae required
« Reply #3 on: February 28, 2013, 06:26:19 PM »

hh:mm won't cut it. It's the square brackets [] what does the magic.

It should then divide by 3 OK - my example of 27:25 / 3 gives 09:08

Cheers,
Peter
« Last Edit: February 28, 2013, 06:29:33 PM by geep »
Logged

oldfogy

  • Helpful
  • Kitizen
  • *
  • Posts: 3568
  • If it ain't broke....... I'll soon fix it.
Re: Exel formulae required
« Reply #4 on: February 28, 2013, 06:38:05 PM »

Ok, thanks again.

I used the [h]:mm for both the individual items and also for the grand total which now does as I want it to do and to add it as the total hours and not days etc, also for single hours such as '3:15' then it does not also place a zero at the beginning as with [hh]:mm = 04:25

Time
3:15
2:00
5:00
7:00
2:15
3:15
4:20
1:00
4:45
32:50 hr
I also managed to get the ' hr ' displayed by using this formulae [h]:mm [$hr-41F]


EDIT
Text above changed.
« Last Edit: February 28, 2013, 08:42:33 PM by oldfogy »
Logged