Counting time

[dmcnab]

Hi all....I have a pretty good understanding of 'time' in Excel, but am puzzled by this...I am trying to calculate elapsed time, and average start & finish times. The attached sample shows 9 morning work sessions for January. Data elsewhere in the workbook records start times for each session, and 'break' times during each session. The total work time and total 'break' time are shown in the sample....the average closing time is 10:55 AM.....it should actually be 10:53 AM, but I think it is somehow counting the first and last minute.....I am unable to make 10:55 calculate at 10:53 by simply deducting 2 minutes from the formula in C12.....my question is how to 'remove' 2 minutes so that the correct closing time is shown (either by formula) or perhaps by formatting the time in C12.....any suggestions? Thanks.

[sdckapr]

If you want to subtract 2 mins from the total:
=IF(C6=0,0,SUM(C7,C9,C11)-TIME(0,2,0))

Can do it. Otherwise the times seem accurate to me. The problem is that the display values are truncated. Your avg start time is 20 secs later than the display, the aver worktime 53 secs longer and the avg break time 47 secs longer. The truncated time is 2:00 mins long...


You work out 9 times for a total of 9:44 avg 1:04:53 (you display only 1:04)
You rest in those 9 periods a total of 2:22, avg 0:15:47 (your display only 0:15)
So the total average workout is 1:20:40 (you assume 1:19)

If your average start time is 9:34:20 AM , with 1:20:40 avg workout you would finish at 10:55:00 AM. (But you see 9:34 + 1:19 = 10:53 due to truncating the seconds).

Display the seconds or round them or actually truncate the values, not just the display to have the display sums match the values.

Steve
PS. To truncate a value (eg in cell A1) to the nearest minute you can use:
=INT(A1*1440)/1440
or
=ROUNDDOWN(A1*1440,0)/1440

To round to the nearest minute:
=ROUND(A1*1440,0)/1440

1440 is the minutes per day (=60 mins/hr * 24 hrs/day). Excel stores dates and times in units of days.