Hours and minutes in one cell

Topic

New Reply

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

Reply | Quote

Replies

You can put them all into hours:

=1.5 +2 +.75+…

or even (if desired)

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

Steve

Reply | Quote

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

Reply | Quote

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)

My Rig: AMD Ryzen 9 5900X 12-Core CPU; ASUS Cross Hair VIII Formula Mobo; Win 11 Pro (64 bit)-(UEFI-booted); 32GB RAM; 2TB Corsair Force Series MP600 Pro 2TB PCIe Gen 4.0 M.2 NVMe SSD. 1TB SAMSUNG 960 EVO M.2 NVME SSD; MSI GeForce RTX 3090 VENTUS 3X 24G OC; Microsoft 365 Home; Condusiv SSDKeeper Professional; Acronis Cyberprotect, VMWare Workstation Pro V17.5. HP 1TB USB SSD External Backup Drive). Dell G-Sync G3223Q 144Hz Monitor.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

@hajankel: Please see the edit in my post.

My Rig: AMD Ryzen 9 5900X 12-Core CPU; ASUS Cross Hair VIII Formula Mobo; Win 11 Pro (64 bit)-(UEFI-booted); 32GB RAM; 2TB Corsair Force Series MP600 Pro 2TB PCIe Gen 4.0 M.2 NVMe SSD. 1TB SAMSUNG 960 EVO M.2 NVME SSD; MSI GeForce RTX 3090 VENTUS 3X 24G OC; Microsoft 365 Home; Condusiv SSDKeeper Professional; Acronis Cyberprotect, VMWare Workstation Pro V17.5. HP 1TB USB SSD External Backup Drive). Dell G-Sync G3223Q 144Hz Monitor.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

@rory: Agreed!

My Rig: AMD Ryzen 9 5900X 12-Core CPU; ASUS Cross Hair VIII Formula Mobo; Win 11 Pro (64 bit)-(UEFI-booted); 32GB RAM; 2TB Corsair Force Series MP600 Pro 2TB PCIe Gen 4.0 M.2 NVMe SSD. 1TB SAMSUNG 960 EVO M.2 NVME SSD; MSI GeForce RTX 3090 VENTUS 3X 24G OC; Microsoft 365 Home; Condusiv SSDKeeper Professional; Acronis Cyberprotect, VMWare Workstation Pro V17.5. HP 1TB USB SSD External Backup Drive). Dell G-Sync G3223Q 144Hz Monitor.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Thanks very much for your help, I will endeavour to try your method today, when I get a spare moment….Steve

Reply | Quote

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

My Rig: AMD Ryzen 9 5900X 12-Core CPU; ASUS Cross Hair VIII Formula Mobo; Win 11 Pro (64 bit)-(UEFI-booted); 32GB RAM; 2TB Corsair Force Series MP600 Pro 2TB PCIe Gen 4.0 M.2 NVMe SSD. 1TB SAMSUNG 960 EVO M.2 NVME SSD; MSI GeForce RTX 3090 VENTUS 3X 24G OC; Microsoft 365 Home; Condusiv SSDKeeper Professional; Acronis Cyberprotect, VMWare Workstation Pro V17.5. HP 1TB USB SSD External Backup Drive). Dell G-Sync G3223Q 144Hz Monitor.

Reply | Quote

It is possible to do add numbers up in one cell, a running total, which sounds like what you want to do. It is possible to add say, a cell with 1:15 in it, then while in that cell, type 2:19 and then type 1:03 and show 4:37 as the total. Is this what you want to do? However, without seeing what you are actually doing with the data in the cells over time, days, weeks, months….. I don’t know if this would be the best solution.

Tim

Reply | Quote

It is possible to add say, a cell with 1:15 in it, then while in that cell, type 2:19 and then type 1:03 and show 4:37 as the total.

In which version of Excel have you tried this and gotten it to work? I get formula errors since 1:15 in a formula is the range of rows 1 thru 15, 2:19 is the range of rows 2 through 19 and so the formulas are not interpreted correctly as discussed in previous posts…

Steve

Reply | Quote

In which version of Excel have you tried this and gotten it to work? I get formula errors since 1:15 in a formula is the range of rows 1 thru 15, 2:19 is the range of rows 2 through 19 and so the formulas are not interpreted correctly as discussed in previous posts…

Steve

Excel 2003. It works with numbers, however, while playing around more with the time format, i.e 1:15, 2:19 etc, it seems the macro doesn’t like just minutes, 0:33 for example. Undoing the total, to clear it, set it to zero, is not easily done when using time format. It works for numbers well, just not for time, so in this case it probably is more trouble for the user than it’s worth.

It’s not really a formula but a macro that stores the cell contents in the comment and adds or subtracts whatever number is entered into that cell with what is in the comment.

Reply | Quote

I apologize. I thought by “possible” you were discussing doing it in a formula (like the OP requested) not creating a macro to read the string.

BTW, I can’t seem to get the macro to work “correctly”, it does not seem to put the running total anywhere. All I seem to get is the text string (which must be manually cleared and fixed with any text errors…)

Steve

Reply | Quote