Excel and minutes

try custom formatting the cell (Tools, format, custom), or failing that the Help function tend to be better than a lot of books on using Excel...

Hi Simon - as Mohamed has indicated the key is formatting - try entering 0:40 in  two cells, then summing them, the colon should make Excel format them as time values and display the result as a time value:

01:20

Hope it works

There is another much more complex way of doing this which gives the answer as text.

If you have the minute values (e.g. 40 and 40) in cells A1 and B1 you could use the following formula:

=TEXT(INT((A1+B1)/60),0)&":"&TEXT((((A1+B1)/60)-INT((A1+B1)/60))*60,0)

This gives you the text result of 1:20.  Simon's method is simpler, more elegant and gives you a number.

Q. What about when the result is greater than 24 hours e.g. A1=12:30, A2=12:30, A3=A1+A2 results in A3 showing '01:00' but you want it to show '25:00'?

A. Change the (custom) cell format from

hh:mm

to

[hh]:mm

Jamie.

--

Jamie - thanks that is perfect and just what I wanted, but it is a bit of a pain entering the data correctly!!!

 It would also appear that if you enter the hours as greater than 24 that the answer in the formula box gives a date result, for example type in "24:01" and the formula box reads: "01/01/1900  00:01:00"

Perhaps the more pertinent point - why on earth are people trying to get Excel to do something it wasn't designed to do in the first place??

Hi Dennis - I completely agree that Excel is often used to do things it's not designed to do (particularly storing and manipulating structured data), however I think doing calculations based on time or data values is pretty much part of what Excel is designed to do. Where the values are coming from or going to may be a wider issue.

Perhaps the more pertinent point - why on earth are people trying to get Excel to do something it wasn't designed to do in the first place??

It's the human factor - staying in the comfort zone. Sure there are better tools. But if I have to go and buy or learn something new, or find someone else to help to solve a quick ad-hoc problem, it will take me longer. It's the Swiss army knife of software - the screwdriver might be the wrong size and I'll cross thread occasionally, but it's always there, and I'm used to it. There's generally a barrier between "users" and "programmers", and Excel nudges the former into the latter when they need to.

Hi Rob - well, we obviously agree on the Swiss army knife analogy! One of my AccountingWeb contributions -  Excess Excel looked in a bit more detail at this issue, and I think reinforces the point you are making, particularly that Excel VBA can act as the bridge between users and programmers - for better or worse. 

The values may look like hours and minutes but they are actually epoch datetime values (instants) e.g. the OP is effectively asking how to add twenty-to-one in the morning on the day of 31 December 1899 to twenty-to-one in the morning on the day of 31 December 1899 <g>. I think a better approach is to *store* decimal hours, or better still IMO integer minutes. A cell formula could then be used to *display* the raw value in an appropriate format.

Jamie.

--

well, we obviously agree on the Swiss army knife analogy!

great minds...or fools who never differ - you decide ;-)

(and no, I hadn't seen your article before)

That sounds like my cue for my annual 'spreadsheets suck' rant. Watch this space - or rather mine ;)

If spreadsheets weren't designed to present and analyse random data, then what were they designed for?