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

[john_B_OBX]

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!

Thanks in advance!

 

[Saba Sabaratnam]

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]

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.