Complex Timesheet workbook Help!

[Danin]

I need help simplifying an overly complicated timesheet. I am not the author of it, just a manipulator.
this workbook contains a payment summary sheet
a breakdown sheet for the payment that pulls daily dockets from
the third worksheet which is for docket entry from multiple areas by multiple people.
the fourth worksheet is all of the information that is sent to the daily docket worksheet.
My issue is I want to create less lines to be input on the daily docket register without causing issues or allowing me to compensate for the issues of me doing this.

I can get the daily normal/overtime/double time for day works formulas. i also managed to add a formula that states if more than 1 hour worked, daily fare is automatically added.
if more than 9.5 hours worked. meal allowance is added.

my problem lies in if they decide to do night work and also for the vehicle types.
Any help would be appreciated.
This register has been used on countless other areas and keeps getting used but no one has bothered to make it more streamlined. i would like to do this as it will be used over 3 year period.

 

[vletm]

If You would like to do this as it will be used over 3 year period.then would next output give Your needed information.

 

[Danin]

i need to enter each docket number singularly. and show the breakdown for each docket to allow me to see the differences at payment time with what the subcontractor sends through. i can only see a screenshot of your idea. and in it, i don't see the docket number. you have input multiple persons of the one docket together. as the docket is arranged it would need me to sit and calculate the hours before entering them from how your worksheet is arranged. I need to keep each persons hours. i have what you have shown arranged. I need something different. Sorry i am not good at explaining what is in my head properly i will upload a manual worksheet to show what I would like it to look like. and highlight what cells I need formulas for.

 

[vletm]

two more samples with some 'Docket' ...

... and those 'night works' won't be challenge ...
but ... but ... I hope that You fill ONLY one line per 'work shift'?
if more ... then someone has done extra work ... humm?

 

[Danin]

i have used the type you are showing me. maybe if you look at the list of items that need to be input you will see my dilemma.
I have been playing and trying to collate the items but i think the more i look at it, the more i see why no one bothered to fix it. If you look at the "List" worksheet you will see what could be entered from just one docket.
also there will never be a total on the docket worksheet page. i put a cell at the top that gives me total of contract, but dockets from a blocked set of dates are pulled over to the payment summary worksheets every month. the dockets are also sent to another workbook each month to join 20 other similar docket registers that are input each month.

I think the complexity of the list of items is the difficulty. if you look at the list worksheet you will see all of the items that need to be put into the docket worksheet.
No totals are required.
i think i will leave it as is. maybe i will talk them into going to access. or paying for an ERP programme. thank you for trying to understand what i required.

 

[Danin]

if you could upload a workable copy of that worksheet you posted i might be able to play with it a little more to manipulate it to work with another worksheet I use.

 

[Danin]

forgot to add my attempt

 

[vletm]

okay ... You continue with 'how to fill those informations' ... yes!
Check 'my "Check This"-sheet.
Basic idea would be that You fill 'start', 'break' & 'finish', okay?
You no need to fill row which can solve from previous, okay?
You should also 'name' other resources that belongs to 'the 1st row',
like 'Light Trucj / Ute' or 'what ever' (if same times then no need to fill times!)
...
I understood that You need 'some summary'-values, or what?
...
Why do You have 'many many' same formulas? (Jun, Aug, Sep ...)
Just one 'delete' will make interesting affect and when would notice that?
...
I'm still interesting to help this ...

 

[Danin]

i don't use this alone, i use this with multiple other users. when it comes time to pay we need to know whether it is day or night, the resource names allow us to see the description at a glance instead of having to click on the number to show us. these worksheets will go to 2000 rows before they are archived and a new one is started. i understand your simplifying but this is for a multi user worksheet. simplifying too much does not allow reports to be properly created from the basic worksheet.
Jun July August are the payment sheets. I did jun july august as examples.
Procedure.
Daily dockets are entered into Docket register.
26 of each month. a months worth of dockets are pushed to the previous month payment summary. eg July 27-August 25 dockets will go to payment summary, they then get compared to the subcontractors payment summary and comments are written next to each line where there are mistakes or errors. it then gets sent back to the subcontractor for their agreement. that worksheet is payment summary. once the amount is agreed upon. the total is pushed to the payment summary page and sent to them for them for final signature to allow us to pay. it is legal demands that require this procedure.

the docket register is not used as an invoicing system. its information is sent to the invoicing system worksheet. the docket register is used for information that is pushed to over 10 systems. including health and safety, quality, legal, insurance. it is used for information and we take that information and utilise it in other worksheets.
I hope that explains why it needs to stay as clear and informative as possible

 

[Danin]

can you please allow for your formulas to be shown when you upload the file. i only see the numbers you have put into W/S "check this"
Sheet 1, 2, 3 require more clicks than needed to see items. and the set up is too simplified for reporting and pivot tables.

 

[Danin]

I worked out why your docket registers don't work for me. once i downloaded i saw you pushed them into pivot tables. please no pivot tables. unrelated to my problem

 

[vletm]

Okay okay ...
I showed You something ... with pivots
You showed me massive formulas something that You would like to make simple.
Many values could get via pivots ... it depends what do You really need.
Those pivots would give quickly some informations.
But, if You don't like ... then no!
Is this 'shared file'? Or as normal file for one user one time?

My 'Check This' should give just an idea that user should fill a form.
'start', 'break' & 'finish ONLY ONCE! and other needed resources!
Why user should have to fill manually OT1.5, OT2 ... and so?
'Excel' should do those! ( as written in #8 )
I need to know what You have to fill for one 'Delivery Docket'-case
before I can make 'form' which would give better image.

After apply the 'form', values would move to 'Docket Register'.
'Docket Register'-sheet won't need any formulas!
as well 'Jul'-'Aug'-'Sep'. Aren't those same? just different periods?
If You could name which period You would like to see (like from 26th to 25th some month) and press [Show]. ... and You could see needed report for that period. It's possible that You could let Excel do some routines! = You fill only once values ... not many times!

Same time, then use 'Docket Register'-informations for other worksheets
... just press the [Button].

If someone has used that some sheets layout has been one kind for years
and now You would like to changes something ... it would be challenge!
If You would like to change something ... it would look different.

I'm still interesting ... I just need to know what You really need.

 

[vletm]

I made a sample for new 'docket'.
It won't save anywhere and so on... but can test something.
It would show that no need massive 'formula-sheets'...
There are some questions too.