Total hours is date is the same

[scott488]

Moning All,

This might take some explaining so I've added a document that may make it easier to understand.

In column A, I have a series of dates.
In column B, I have a series of hours and minutes

What I want to be able to do is total the number of hours and minutes for each day and display the results in column C.

Hope that makes sense!

Scott

[arthurbr]

Perhaps

 =SUMPRODUCT(($A$1:$A$20=DATEVALUE("04/01/11"))*($B$1:$B$20))

and format as [h]:mm:ss

[scott488]

Authurbr,

My apologies, I have changed the format to [h]:mm:ss

That works for the one cell with the date shown (04/01/11), but I would like it to work this out across multiple dates.

Unless I've missed something?

I've added a new example to show what I would prefer to see once the formula is copied across the cells in column C

Scott

[Richard Buttrey]

Hi,

One way, and since your times are text rather than proper time numbers (i.e. decimals of a day) we need some string conversion involved, is as follows.

Add an extra row so that the data starts in A2

Now in C2 which should be formatted as time ("hh:mm") enter the following formula and copy down.

=(VALUE(LEFT(B2,2))*60+VALUE(RIGHT(B2,2)))/1440+IF(A2=A1,C1,0)

This will give you a cumulative daily total.

What I suspect you're really after is just the total for each day. In which case extract a unique list of dates into say column E, then change the formula above and leave out the last bit with the +IF

Now you can use SUMIF() to sum the daily totals from column C.

Regards

[arthurbr]

Like the attached perhaps?

[scott488]

arthurbr,

Exactly like that, thank you very much for your time and effort.

Regards
Scott