Excel time function help please
Thread Starter
Join Date: Nov 2000
Location: Perm any one from 3 !
Posts: 310
Likes: 0
Received 0 Likes
on
0 Posts
Excel time function help please
I always cheat when doing time based calculations and go staright to decimal hours or use a number xxpointxx to represent a time which I subseqently convert to decimal using a TRUNC based formula to treat the hours differently to the minutes.
However this time I am linking a whole pile of figures into spreadsheet, but the originals are in HH:MM (xxcolonXX) format.
Help
However this time I am linking a whole pile of figures into spreadsheet, but the originals are in HH:MM (xxcolonXX) format.
Help
Last edited by TimS; 5th Sep 2007 at 07:27. Reason: correcting my dreadful typing
Join Date: Sep 2001
Location: UK
Posts: 741
Likes: 0
Received 0 Likes
on
0 Posts
I hate it when they shout "Time" !
Not 100% sure what you're asking - do you want to input times onto your spreadsheet in HH:MM format and then convert these to decimal time with a formula, e.g where 12:30 ( 12 Hrs 30 Minutes ) is the input value, which then needs to be converted to 12.5 ( twelve and a half ) hours ?
If so make sure the cell ( e.g. A1 ) containing the HH:MM data is formatted as a time cell, then use the formula =HOUR(A1)+MINUTE(A1)/60 to convert to decimal hours.
Hope this is what you wanted.
If so make sure the cell ( e.g. A1 ) containing the HH:MM data is formatted as a time cell, then use the formula =HOUR(A1)+MINUTE(A1)/60 to convert to decimal hours.
Hope this is what you wanted.
There is the TIMEVALUE function, that takes a string and converts it into a time. I just tried it, it seems to handle "hh:mm" OK.
It returns a standard Excel date/time value, where 1 = 1 day. So you can multiply the result by 24 to get hours, or 86400 to get seconds.
It returns a standard Excel date/time value, where 1 = 1 day. So you can multiply the result by 24 to get hours, or 86400 to get seconds.
Join Date: Mar 2005
Location: Europe
Posts: 132
Likes: 0
Received 0 Likes
on
0 Posts
I too am not sure of the question, but this is how I add hours and minutes in Excel.
Enter the formula below in the hours cell,
=(SUM(G4:G14))+INT((SUM(H4:H14)/60))
Enter the formula below in the minute cell,
=MOD(SUM(H4:H14),60)
The formula set out above adds the hours in column G (hours) 4 to 14 and minutes in column H (minutes) 4 to 14. There is no problem with adding hours and minutes to whatever total you wish. I am using this system to keep a simple electronic log book and my duty hours. The cells in the formula are just the ones that I copied direct and obviously are for illustration only.
Enter the formula below in the hours cell,
=(SUM(G4:G14))+INT((SUM(H4:H14)/60))
Enter the formula below in the minute cell,
=MOD(SUM(H4:H14),60)
The formula set out above adds the hours in column G (hours) 4 to 14 and minutes in column H (minutes) 4 to 14. There is no problem with adding hours and minutes to whatever total you wish. I am using this system to keep a simple electronic log book and my duty hours. The cells in the formula are just the ones that I copied direct and obviously are for illustration only.
Last edited by check; 7th Sep 2007 at 13:21.
Join Date: Apr 2003
Location: U.K./nigeria
Posts: 114
Likes: 0
Received 0 Likes
on
0 Posts
time in excel
I have used the TIME function which works well for adding hours/minutes/seconds depending on other formatting, but I find that it will not add total of hours more than 24, i.e, a day, I want to add, say 1:30 to 5645:20 and it does not give me 5646:50 as i might expect, any way of getting Excel to ignore the day and just add hours and minutes in the way you would probably require in a simple log book using the TIME function to more than 24 hours?
Join Date: Nov 2005
Location: right here
Posts: 342
Likes: 0
Received 0 Likes
on
0 Posts
..same problem over here
i use fractions, set in the formating-menu to x/60
e.g. 1hr37 min goes 1 37/60
not elegant, but works
u can also put in "real" numbers like 2,5 which then are converted to 2 30/60....
e.g. 1hr37 min goes 1 37/60
not elegant, but works
u can also put in "real" numbers like 2,5 which then are converted to 2 30/60....
Join Date: Jun 2003
Location: Scotland
Posts: 96
Likes: 0
Received 0 Likes
on
0 Posts
There are numerous formats for cell contents but, to my recollection, hh:mm is not a standard one. It is straightforward enough to define a custom cell format of hh:mm then simply input your data as that format and excel deals with the rest of it.
Join Date: Feb 2000
Location: asia
Posts: 542
Likes: 0
Received 0 Likes
on
0 Posts
Time
if you use the format [h]:mm:ss for a cell, you can enter numbers like 3:20 which will be 3 hours 20 minutes, and also numbers up 9999:20 which will be 9999hours and 20minutes.
Seems only up to 4 digits of hours can be input in that format, although if you want to display more digits of hours then format the cell as [hhhhhhhh]:mm:ss with as many h as required.
Remember you can also add dates and times quite happily in excel, for instance
a cell formatted as [h]:mm:mm containing 24:00 and a cell formatted as dd/mm/yyy containing 20/07/2007 and summed will give the result 21/07/2007 if the format is dd/mm/yyy
all dates start from 1900, and any date without a time assumes 00:00:00
Don't need any of the complicated stuff above at all.
(I am using excel 2003 by the way)
For another little tip, try this.
cell a1 format [h]:mm:ss
cell a2 format as number with 2 decimal places, content =a1
cell a3 format as 0.00"hours", content =a2*24
then type 24:00 into cell a1, a2 will display 1.00, a3 will display 24.00hours
type 12:00 into cell a1, a2 will display .5, a3 12.00hours
type 12.30 into a1, a2 will display 0.52, a3 12.50hours
why bother with anything more complicated?
let me know if you don't understand or need more help.
Seems only up to 4 digits of hours can be input in that format, although if you want to display more digits of hours then format the cell as [hhhhhhhh]:mm:ss with as many h as required.
Remember you can also add dates and times quite happily in excel, for instance
a cell formatted as [h]:mm:mm containing 24:00 and a cell formatted as dd/mm/yyy containing 20/07/2007 and summed will give the result 21/07/2007 if the format is dd/mm/yyy
all dates start from 1900, and any date without a time assumes 00:00:00
Don't need any of the complicated stuff above at all.
(I am using excel 2003 by the way)
For another little tip, try this.
cell a1 format [h]:mm:ss
cell a2 format as number with 2 decimal places, content =a1
cell a3 format as 0.00"hours", content =a2*24
then type 24:00 into cell a1, a2 will display 1.00, a3 will display 24.00hours
type 12:00 into cell a1, a2 will display .5, a3 12.00hours
type 12.30 into a1, a2 will display 0.52, a3 12.50hours
why bother with anything more complicated?
let me know if you don't understand or need more help.
Last edited by stickyb; 12th Sep 2007 at 11:24. Reason: added a bit more
Good God, you don't half make things complicated! Why didn't you just ask your little brother in the first place?!
By the way, if you read this then send a text or email so that we know you are still alive.
TeeS
By the way, if you read this then send a text or email so that we know you are still alive.
TeeS
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
Clarify the question a little, and I think the answer will be forthcoming.
Excel can do all the various calculations, whether calculating hours and minutes from dates and times, or adding hours and minutes - but you need to format the cells the right way to do that, or the answer will be rhubarb.
Excel can do all the various calculations, whether calculating hours and minutes from dates and times, or adding hours and minutes - but you need to format the cells the right way to do that, or the answer will be rhubarb.
Not sure what the question is either - how about expressing it in terms of (a) what you have (input), and (b) what you want (output)? For example, if you're starting with text that looks like times, and you want it turned in to time values that Excel can use, then you want TIMEVALUE function I mentioned earlier.