How to calculate h and min with Excel?
Thread Starter
Join Date: Aug 2000
Location: formally Alamo battleground, now the crocodile with palm trees!
Posts: 965
Likes: 0
Received 0 Likes
on
0 Posts
How to calculate h and min with Excel?
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
---------
Yes, I still know how to do this in my head, Excel however ....
7 7 7 7
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
---------
Yes, I still know how to do this in my head, Excel however ....7 7 7 7
Join Date: Dec 1999
Location: Where?
Posts: 145
Likes: 0
Received 0 Likes
on
0 Posts
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,
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,
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.
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.
Thread Starter
Join Date: Aug 2000
Location: formally Alamo battleground, now the crocodile with palm trees!
Posts: 965
Likes: 0
Received 0 Likes
on
0 Posts
Hamrah is correct!
It took me a while to restart this project . 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
It took me a while to restart this project
. 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
Join Date: Apr 1999
Location: www.e-jets.org
Posts: 154
Likes: 0
Received 0 Likes
on
0 Posts
Do you still need help with this,
I can help with any questions you have
http://www.bigfoot.com/~P-air
I can help with any questions you have
http://www.bigfoot.com/~P-air
Join Date: Jan 2001
Location: Far Far East - and a good touch of South
Posts: 29
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Join Date: Jan 2003
Location: SX in SX in UK
Posts: 1,082
Likes: 0
Received 0 Likes
on
0 Posts
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
BUT!! if you have Windows XP, this particular format has been removed and the time will only total up to 23:59
