# 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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### 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
140
Replies
1
Views
146
Replies
21
Views
703
Replies
3
Views
240
Replies
3
Views
297

1,181,598
Messages
5,930,805
Members
436,761
Latest member
mintwaxed

### 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