# Timesheet that uses SUMIF to kick out time spent in a given day on a specific matter #

#### john_B_OBX

##### New Member
I am trying to create a timesheet where I am able populate the timesheet throughout the day with different client matters I am working on and have the model output the hours spent on a specific matter. To accomplish this (using columns A:C as an example in the current model below), I am currently using SUMIF to sum the 5-minute intervals in column B if the specific C/M number appears in column C... this actually works pretty well save for one issue that I am trying to fix: summing column B (with the 5-minute intervals) instead of column A creates the issue where, for example, if I am working on a specific matter for a specific interval (start time A, end time B) the output will always be an extra 5 minutes... for example, if I put C/M number 55 for the interval 8:00 AM to 9:00 PM, it will kick out 1.08 hours (65 minutes) instead of 1 hour (60 minutes).... this issue is compounded if there are multiple start/stop times throughout the day where I am working on a specific matter. Let's say I work on matter 55 from 8:00 AM to 9:00 AM and then 2:00 PM to 3:00 PM - I want the SUMIF function to output 2 hours, and not 2.17 as it it currently doing... the obvious fix would seem to be changing the function to sum the difference between the two different start/stop times (8:00 AM to 9:00 AM and 2:00 PM to 3:00 PM), but I am not sure how to accomplish this and would greatly appreciate any input!

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Saba Sabaratnam

##### Active Member
I am not sure if you are willing to use a helper column as shown below.

If so,

Enter the following formula in D2 and copy it down

=IF(OR(ISBLANK(C2),ISBLANK(C1))=FALSE,IF(C2=C1,B2,""),"")

Then you can use column D to carry out your summation of time.

Kind regards

Saba

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Perhaps I am wrong, but it seems to me that perhaps you are filling in the timesheet incorrectly.
Does that first group of '55' values in column C indicate that you worked on that matter for an hour from 8:00 am to 9:00 am?
If so, how would you complete the timesheet if you immediately at 9:00 am started working on matter 99 given that the 9:00 am time slot is already occupied?
Shouldn't you only be marking the matter at the start of each 5-minute period?

Please consider investigating XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Replies
2
Views
355
Replies
3
Views
208
Replies
1
Views
217
Replies
6
Views
54
Replies
8
Views
142

1,191,005
Messages
5,984,120
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back