Excel time function help please

[TimS]

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

[Coconutty]

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.

http://i34.photobucket.com/albums/d129/coconut11/Coconutty.jpg

[bnt]

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.

[check]

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.

[bluesafari]

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?

[FCS Explorer]

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....

[Beagle-eye]

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. :ok:

[stickyb]

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.

[TeeS]

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

[Keef]

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.

[bnt]

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.