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.
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!
=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....
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.
| Date | Start | Finish | Hours | Project # | Project Title | Description |
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/2021 | 08:45 | 09:00 | 00:15 |
| 24/11/2021 | 09:00 | 10:00 | 01:00 |
| 24/11/2021 | 10:00 | 13:00 | 03:00 |
| 24/11/2021 | 14:00 | 15:45 | 01:45 |
| 24/11/2021 | 15:45 | 16:15 | 00:30 |
| 24/11/2021 | 16:15 | 17:00 | 00: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....
