Timesheets - Extracting Key Data

G Wiz

New Member
Jan 6, 2022
Office Version
  1. 365
  1. Windows

This is my first post.

I'm not a massive Excel user, as I work in Architecture and Construction, however I have utilised Excel to do some clever things in the past and love what it can do with data.

I currently have a timeheets document, that I need to overhaul massively so I can extract key data quickly.

At the moment the timesheets is setup with the following headers which have filters placed on them.

DateStartFinishHoursProject #Project TitleDescription

The Project # column has data validation linked to another sheet within workbook, that auto populates the project title based on the selection in the Project # box.

The timesheets are generally completed with multiple entries per day, I don't always do 9-5, I also do freelance and other work, and time is split switching between projects, so the time switching between is logged accordingly so some days may look like this:


The time formatting is hh:mm.

What I need to be able to do is, filter a set number of days for example, most often a month range and obtain:

How many days worked [I cannot simply calculate this as from above I have multiple entries within one day]
Total number of hours worked in a day
Total number of hours worked for the filtered results
Total number of hours worked for each project

These figures will need to return values based on filtered data.

I have the following formula to calculate total number of hours which works OK in some cases, but I ran this today on hours worked in December and the figure is over twice the actual amount!


I've tried following numerous examples online, but it's still giving problems, plus I haven't even started with the other 2 problems.

I'd love some help on this, I guess I may be over complicating what is generally a simple document, however I'd really like to get this to work as a bespoke document as it is much better than using any template.

Many thanks in advance....

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi - do you still need help with this?
Upvote 0
On the off-chance that you do, except for the filtered data point, is this broadly what you wanted?

I should point out that the subtotals use custom number formatting: [hh]:mm

204/02/2022Project A09:0009:1500:15Project A09:20
304/02/2022Project B10:0010:4500:45Project B06:50
404/02/2022Project C10:4513:0002:15Project C13:15SubTotal29:25
504/02/2022Project A13:1013:2000:10
604/02/2022Project A13:3014:1000:4004/02/202206:00
704/02/2022Project B15:0015:1000:1005/02/202200:15
804/02/2022Project C15:1517:0001:4506/02/202200:45
905/02/2022Project A09:0009:1500:1507/02/202202:15
1006/02/2022Project B10:0010:4500:4514/02/202203:50
1107/02/2022Project C10:4513:0002:1515/02/202204:10
1214/02/2022Project A13:1017:0003:5016/02/202205:10
1315/02/2022Project A10:0014:1004:1017/02/202207:00SubTotal29:25
1416/02/2022Project B10:0015:1005:10
1517/02/2022Project C10:0017:0007:00
Cell Formulas
Upvote 0

Forum statistics

Latest member

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