Hours and minutes in one cell

[hajankel]

Hi to all

I require a function in excel which will do the following:

Add together in one cell; example 1 and a half hours + 2 hours + 45 minutes + and so on.

I know how to do this in a column of cells but NOT a list of hours and minutes in a single cell, it always comes up with an error flag.

any help on this one guys....

regards

Steve Gulliver - Southampton UK

[sdckapr]

You can put them all into hours:

=1.5 +2 +.75+...

or even (if desired)

=1+30/60 + 2 + 45/60 +....

Steve

[hajankel]

You can put them all into hours:

=1.5 +2 +.75+...

or even (if desired)

=1+30/60 + 2 + 45/60 +....

Steve

Thanks very much for the rapid response, that would certainly work up to a point. I think I may have phrased it incorrectly with a poor example.

I would like to be able to add up in hours and minutes and set of number such as. 1 hr 32 mins + 3 hrs 19 mins + 19 hrs 13 mins, not just easily rounded numbers like .25 or .45 etc.. I am attempting to get the spreadsheet to work out the answer for me, I am aware that I can just make 3hrs 19 mins into 199mins then divide by 60. However I am looking for a way of just inserting the hours and minutes into the cell as I have them put in front of me by a work colleague.

Hope I have made it a little clearer to all...

kind regards

Steve Gulliver - Southampton UK

[petesmst]

I am not sure what you want: If you type =1+30/60+2+23/60 into the formual bar, you will get the answer in the selected cell. If you type (for example) 1 into cell A1, =30/60 into cell A2, 2 into cell A3 and =23/60 into cell A4 and then type =sum(A1:A4) into cell A5 you will get the same answer. Either way, excel will calculate the total for you. To enter a whole hour and a number of minutes, just type in minutes/60 and add to the whole number hour wherever applicable, either in the formula bar; or in adjacent cells and then sum all cells.

Alternatively: (Neater) Format your cells for the entry of degrees, minutes, seconds (slightly modified) - see below. You then enter your data in the format hh:mm:ss (hours:minutes:seconds). Adding (summing) such data will yield a correct answer (as opposed to formatting the cells in the "time" format which would always adjust the answer to fit in to the 24-hour clock). To do this, select: format, cells, time, custom. In the space beneath "type", enter the following: [hh] :mm :ss

The [] tells excel not to roll-over the time to confirm to the 24-hour clock. For example, the time value 30:40:50 would normally be displayed as 06:40:50. To correct this, you can use a number format of [hh]:mm:ss. The square brackets [ ] instruct Excel not to roll over the displayed value as a "time of day" value.

Your cells will now accept data entered as follows: 04:32:01 (4 hours 32 minutes 1 second). See attached example

EDIT:
You cannot do all the summing in the SAME cell "on the fly" which is what I think you wanted to do. Excel uses formula to add, subtract, etc, discrete data in cells and then provides the answer in another cell. You will need to enter each person's time sheet time in a different cell and then use the "sum" function to add the times together (see my attached example)

[rory]

Can I ask why you would want to do this in one cell? You're pretty much defeating Excel that way.

[hajankel]

Can I ask why you would want to do this in one cell? You're pretty much defeating Excel that way.

Hi again all..

Okay I will attempt to make myself clearer...

When I arrive in my office in the morning I am confronted with dozens of timesheets, on these timesheets it lists the drivers hours and ALSO their 'Periods of Availability'. Whilst inputting their 'POA' data I am trying to insert the hours and minutes into my spreadsheet in the format in which it is shown on their timesheets. ie:

Monday - 1hr 42 minutes
Tuesday - 2hr 32minutes
Wednesday 3hr 49 minutes...etc

I do not wish to put in the number as: =1+42/60+2+32/60..etc
I would like to insert as: 1:42+2:32+3:49..etc

This can partially be done in the cell formatting using [h]:ss command, but it will NOT allow me to use the same formatting to add them all together in the same cell..

I hope you understand....

kind regards

Steve Gulliver - Southampton UK

[hajankel]

I am not sure what you want: If you type =1+30/60+2+23/60 into the formual bar, you will get the answer in the selected cell. If you type (for example) 1 into cell A1, =30/60 into cell A2, 2 into cell A3 and =23/60 into cell A4 and then type =sum(A1:A4) into cell A5 you will get the same answer. Either way, excel will calculate the total for you. To enter a whole hour and a number of minutes, just type in minutes/60 and add to the whole number hour wherever applicable, either in the formula bar; or in adjacent cells and then sum all cells.

Alternatively: (Neater) Format your cells for the entry of degrees, minutes, seconds (slightly modified) - see below. You then enter your data in the format hh:mm:ss (hours:minutes:seconds). Adding (summing) such data will yield a correct answer (as opposed to formatting the cells in the "time" format which would always adjust the answer to fit in to the 24-hour clock). To do this, select: format, cells, time, custom. In the space beneath "type", enter the following: [hh] mm' ss\"

Your cells will now accept data entered as follows: 04:32 (4 hours 32 minutes). See attached example

Thanks very much Peter for taking the timeout to look at my query, and thanks for the download, as I mentioned earlier I am familiar with adding them up in columns, the difficulty I face is trying to add up the hours and minutes in a single cell. Please have a look at my latest posting for more details...kind regards..Steve

[petesmst]

@hajankel: Please see the edit in my post.

[sdckapr]

The direct way you want to do, can NOT be done.

If entered into a cell direectly a number with colons ( is understood by excel to be part of a date. When enetered, excel will do the math and convert the time value to a number representing a number of days (all excel time is in units of days), and then format the cell to display similar to how it was entered. If the entry can not be understood by excel as a date it will be presumed to be text and will not be converted to a number.

When you place several values in a cell as a FORMULA [starting with an equal (=), a plus (+), or a minus(-)], excel does not convert them but tries to understand what you enter as a formula. Colons in formulas represent ranges. Other than mathematical operations, and parentheses only a period (.) is used to represent a placeholder in a number. No commas, no colons: either will give an error in a formula (unless part of text or representing a range or parameter).

The simplest way to do what you ask is to add the hours as I suggested:
=1+30/60+2+45/60...

It does not require only the "simple" fractions but will work with all the minutes. You can even include seconds by dividing them by 3600. But note the hours can not be formatted as hours since they are not days. If you do this way and want to format as time in excel they need to be days so you would need something more like:
=(1+30/60+2+45/60+ ...)/24
to convert the hours to days...

Since you are entering the individual items anyway, I don't see the advantage of one cell entry. For faster data entry, having intermediate cells for hours and minutes for each day and then adding them up and putting the sum into the cell would be much faster:
=(sum(hours) + sum(minutes)/60)/24
will give the days and formatted as [h]:mm will display the elapsed time in hours and minutes...

Steve

[hajankel]

Well first and foremost,

thanks very much to all of you kind people for helping me out with this problem, your all very kind

I would just like to add that wouldn't you guys think that a piece of software that is soooo sophisticated would indeed be able to do what I have requested it to do in the manner in which I asked it.... and to all the doubters out there who will come back with this can NEVER be done "remember NOTHING is impossible"...


thanks to you all once again....Hare Krysna!!

[rory]

No, because by using a different layout, it's a simple matter. Whilst you can often force Excel to work the way you think it should do, there's usually an easier way and more often than not, it's a better way.

[petesmst]

@rory: Agreed!

[sdckapr]

It could be done, in some manner. If entered as text (not a formula) one could write a routine that parsed the text, then did the mathematical manipulations and replaced the text with the number. But as it has been pointed out there are easier ways...

Steve

[sdckapr]

Here is a method that is "almost" what you desire.

In a module add the following function:
[codebox]
Option Explicit
Function TimeSum(str As String)
Dim AWF As WorksheetFunction
Set AWF = Application.WorksheetFunction
TimeSum = Evaluate("=timevalue(" & Chr(34) & _
AWF.Substitute(str, "+", " AM" & Chr(34) & _
")+timevalue(" & Chr(34)) & " AM" & Chr(34) & ")")
Set AWF = Nothing
End Function
[/codebox]

Then in a cell you can use it to sum the times. Use it like:
=Timesum("1:30+2+0:45")

This is close to what you wanted only in addition to entering all the values you must preface the function name and enter the list of times as a string. If desired the function name can be shortened (change it both places in the module...)

Steve

[petesmst]

@sdckapr: Neat! I have learned something new. That's the great benefit of this forum. Thanks.