How to calculate h and min with Excel?

[Squawk7777]

I am trying to design my own logbook with Excel. Being a total inexperienced user of the so-called Master of Spreadsheet program, I find myself unable to pass the first "programming" hurdle.

It is fairly easy to make calculations with Excel, not when it comes to time units however. What settings/formulae must be used to do the following:

01:45
00:56
---------
:rolleyes: Yes, I still know how to do this in my head, Excel however ....

7 7 7 7

[Hamrah]

If you format the columns (or rows) with the following "Custom" format [hh]:mm , it will add up the hours correctly.

H

[idgas]

This may also help

To calculate hours on a dailty basis, when finish time is after 24:00, use the following:

Format cells to HH:mm

Cell A1 is Start Time
Cell B1 is Finish Time

=IF(B1-A1>0,(B1-A1),(B1-A1)+1)

It works for me in Excel & Works when total time is less than 24 hours.

Hope this helps and makes sense.

Cheers, :cool:

[criticalmass]

Haven't got Excel but an idea occurs...

If possible, try expressing times in angular measure (degrees, minutes and seconds). If Excel will handle angular measure in this format, otherwise remember the conversion from radians to degrees (2 times pi radians is 360 degrees).

These will total up to so many degrees, minutes and seconds. Just convert the degrees portion to hours and that might get you going in the right direction, especially if you are trying to total up logbook hours (which I assume is something like the intended function).

Dunno if this is helpful, but it is a lateral thinking approach which has worked for me in the past on a slightly different application.:)

[Squawk7777]

Hamrah is correct!

It took me a while to restart this project :rolleyes: . The key to correct calculations is to put the hours in brackets [hh]:mm. If you don't do it, Excel will not recognise it as elapsed hours, therefore you cannot exceed 24 hours.

7 7 7 7

[P-air]

Do you still need help with this,

I can help with any questions you have


http://www.bigfoot.com/~P-air

[Dibble&Grub]

I have been using a home grown logbook package for 15 years now and the problems of Hours and Minutes (base 60) math is a sod. Particularly as the numbers grow. (Rounding errors etc). The only successful tactic I found (and it is totally reliable on even the most pedestrian system and software) - is to go back to minutes and do the Maths at that point and then return the answer to HH.MM format.

Here are Excell type routines : Assume HH.MM in A1

B1 : =(INT(A1)*60)+((A1-INT(A1))*100) ' This gets you MM

To recombine B1 to HH.MM

C1 : =(INT(B1/60))+(MOD(B1,60)/100) ' This gets you HH.MM

Note HH.MM format with a decimal point - NOT a colon.

Just put the Minutes into a seperate column and do all the maths there. Enjoy...

D&G

[stickyb]

Just to bring this one back to the top - note the comments about HH in brackets

[Kolibear]

If you format the cells using the following :- Format - Cells - Time then pick the example '30.55.7' this will quite happily add up you hours past 100hr - at least it does on my electro-logbook.

BUT!! if you have Windows XP, this particular format has been removed and the time will only total up to 23:59 :(

[Llademos]

Excel 97 users should use [h]:mm as it doesn't seem to like two h's.