I need to record hours & minutes on a daily basis, and then calculate totals on a weekly/monthly basis total. Using the normal number formats doesn't work, because 60 minutes is shown as 0.60, not 1.00. So, 40 plus 40 is calculated as 80 not 1.20. Using the date/time formats doesnt work either, because the system wants to show the date. Similarly

Custom Format hh:mm doesn't work, because the resulting figures are shown as a date.

I know it's possible, because I've done it before. I just can't remember how

All help & ideas gratefully received.

Thanks

Jonathan Taylor

25th Dec 2005 01:01

0

6

91746

## Replies

## Please login or register to join the discussion.

You may use following macro for summing time

Note:

Use decimal numbers to record time

Use first argument = 0 for hh:mm result = 1 for decmal result.

As lines in this comments are wrapped at "wrong" place the macro may not work, if desired I can send you an excel sheet with examples, let me know your e-mail.

Zubair Edhy

[email protected]

Macro:

Function timesum(ctype, ParamArray times())

'ctype 0 = Time sum, 1 = Decimal sum

If Not (ctype = 0 Or ctype = 1) Then _

timesum = "First argument should be 0 or 1": Exit Function

For i = 0 To UBound(times())

If IsObject(times(i)) Then

For Each c In times(i)

If (IsNumeric(c.Value) And c.Value <> 0) Then

timesum = timesum + (Int(Abs(c.Value)) + (Abs(c.Value) - Int(Abs(c.Value))) / 0.6) * c.Value / Abs(c.Value)

End If

Next

Else

If (IsNumeric(times(i)) And times(i) <> 0) Then

timesum = timesum + (Int(Abs(times(i))) + (Abs(times(i)) - Int(Abs(times(i)))) / 0.6) * times(i) / Abs(times(i))

End If

End If

Next

timesum = WorksheetFunction.Round(timesum, 4)

If (ctype = 0 And timesum <> 0) Then timesum = (Int(Abs(timesum)) + (Abs(timesum) - Int(Abs(timesum))) * 0.6) * timesum / Abs(timesum)

timesum = WorksheetFunction.Round(timesum, 2)

End Function

[hh]:mm

You were on the right lines using custom number formats but you need to put square brackets around the hh to show the correct result when summing time values exceeding 24 hours - i.e. use a custom number format of [hh]:mm for the total cell.

David Wagstaffe

Payexcel Limited

http://www.payexcel.co.uk

Time formats are a pain

The time formats do actually work. If a cell is formatted hh:mm, then you have to enter the hours and the colon - even if hours are zero. So, to enter 45 mins enter 0:45 and the display will show 00:45.

Where it gets difficult is with the adding up. If the total (from the SUM function) exceeds 24 hours, you can get a decimal total by multiplying the result by 24 and changing the cell format back to General - for example:

=SUM(A1:D1)*24 with the cell format switched back to a number.

Of course, this is no good because you want the total in hours and minutes. Here's where it gets complicated. You can use functions to parse the decimal result or you can say to hell with time functions - there has to be an easier way!

I use separate cells for entry of hours & minutes (use data validation to block a minutes entry>60). Then suppose I have Col A & B for hours & minutes respectively and 3 rows of data in rows 1 to 3, the totals are:

Hours (A4): =SUM(A1:A3)+TRUNC(SUM(B1:B3)/60)

Mins (B4): =SUM(B1:B3)-TRUNC(SUM(B1:B3)/60)*60

Similar formulae work across worksheets.

No doubt you'll get some neater answers than this, but if all else fails, I'd abandon the Excel time formats.

Steve

Hours & minutes in Excel

Excel deals perfectly with Hours and minutes providing the hours are input first (even if thy are zero) and are then followed by a colon (:).

Excel assumes that the seconds are nil unless another : is entered and a value of seconds is input. If these are then summed the correct hours and minutes total is diplayed eg

1:30 and 1:30 gives an answer of 3:00:00

Time to Money calculations

Before we billed - using a cheap proprietry programme (Timeslice) we constructed weekly / monthly xl spreadsheets. Here is an extract from one of them:

row/col b c d e f

2 start end total

3 11:00 11:30 00:30

4 00:01 13:21 13:20

5 00:01 13:21 13:20

6 27:10:00 £0.90 £1,467.00

The start and end times are formatted hh:mm in the Custom format.

Formula in d3 to d5 is =C3-B3 etc downwards.

Formula in d6 is =SUM(D3:D5)and formatted in the Time format - select 37:30:55.

Cell f6 (actually is in e6)is money

Cell g6, the £1467, is formula =(E6)*(60*(((D6-INT(D6))*24)+(INT(D6)*24)))

Calculating with time values

You need to remember with time values that, regardless of how the cell is formatted, Excel holds the value as a number representing the fraction of a day - e.g. 12 hours is held as 0.5 and 36 hours is held as 1.5.

Taking Stephen's example, if you have cell D6 formatted to display a time value of 27:10 (being held in Excel as 1.131944 days) and wish to multiply it by a rate of 0.90 pence per minute shown in cell E6 you would need a formula in, say cell G6, of =D6*24*60*E6 to give a result of 1,467 pounds. (Just make sure that cell G6 is formatted as a currency or other regular number format if it defaults to a time value format.)

David Wagstaffe

Payexcel Limited

http://www.payexcel.co.uk