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

john_B_OBX

New Member
Joined
Jun 15, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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!


1623795985066.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
397
Office Version
  1. 365
  2. 2010
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.

1623813171371.png


Kind regards

Saba
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,674
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,817
Messages
5,708,760
Members
421,588
Latest member
Wawie

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top