Timesheets - Extracting Key Data

G Wiz

New Member
Joined
Jan 6, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi,

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:

24/11/202108:4509:0000:15
24/11/202109:0010:0001:00
24/11/202110:0013:0003:00
24/11/202114:0015:4501:45
24/11/202115:4516:1500:30
24/11/202116:1517:0000:45

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!

=SUBTOTAL(109,D8:D3000)

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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

G Wiz

New Member
Joined
Jan 6, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Anyone out there can help with this please?
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
992
Office Version
  1. 365
Platform
  1. Windows
Hi - do you still need help with this?
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
992
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel.xlsm
BCDEFGHIJKLMN
1DateProjectStartFinishDuration
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
Sheet1
Cell Formulas
RangeFormula
J2:J4J2=SUMIF($C$2:$C$15,I2,$F$2:$F$15)
M4,M13M4=SUBTOTAL(109,$F$2:$F$17)
B3:B8B3=B2
B9:B11,B13:B15B9=B8+1
J6:J13J6=SUMIF($B$2:$B$15,I6,$F$2:$F$15)
F2:F15F2=E2-D2
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,255
Messages
5,836,274
Members
430,414
Latest member
ayla

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