Complex Daily Docket Excel Workbook

Danin

New Member
Joined
Sep 16, 2016
Messages
6
Large project traffic control register. It is quite a frustrating issue due to so many lines per person.
Workbook with 2 worksheets. WS1 contains list of items we use to allow input on WS2 which is the daily docket register.
The numbers equal lines they correlate to in list as an example
1. is the rate for the first 8 hours are normal rates
2. is the rate for the next 1.5 hours are overtime
3.is the rate for hours over 9.5 are dble overtime
7. is the rate for vehicle
8 is rate for attenuated signage
10. is rate for meal allowance once a person works over 9.5 hours.
11. is rate for daily fare. one per person per day regardless of hours worked.

you can imagine how frustrating it is to enter a timesheet that has 5 persons working for a day.
Row1. I would enter "7" this would calculate the hours of the vehicle to its hourly rate. 6:00am -18:30.
Row2. i would enter "11" and skip the hours and just put "5" total persons into total hours cell. this then calculates rate and quantity of persons.
Row3. I would enter "1" first persons hours 6:30 - 30minbreak- 15:00 - 8hours
Row4. I enter "2" firs persons name 15:00 - 16:30. 1.5 overtime
Row5. I enter "3" first persons name 16:30-18:00 1.5 overtime. ** this line now allows me to enter the meal allowance for anyone who has worked over 9.5 hours.
I would repeat Row 3 to 5 - 5 times to equal 5 people.
after i enter all persons i would then enter meal allowance in similar way i enter daily fare. one row ignore start time finish time and just put 5 into total to allow calculation to occur.

We need to be able to check the hours each month for a payment plan. the more i think about it i think that if i set up a row for one person with columns that show the costs instead of rows.
Name Start Break Finish NrmlTime 1.5 Dbl DailyFare Meal Allowance
Person1 6:30 0:30 18:00 - can you help me with formulas to get all of the information I need out of those hours to be put into columns. instead of having to do a line by line breakdown.
I want to allow it to automatically add a meal allowance.
I want it to automatically add a daily fare to each person.

I can then create a vehicle row out of similar formulas created for persons. I can use the persons row formulas and manipulate them to suit vehicle items. send me message and i can give you example of excel register if that would help.
Thanks in advance for any help.
 

Danin

New Member
Joined
Sep 16, 2016
Messages
6
=MAX($I$3,H9-I9-J9)
i am using this formula in the "Double Time Cell" is there a more concise formula?
Start break Finish
6:00 0:30 20:00
total hours formula =((G10-E10+(G10<E10))-F10)*24
normal hours formula
=MIN($I$1,H10)
1.5 overtime formula
=MIN($I$2,H10-I10)
double time formula
=MAX($I$3,H10-I10-J10)

I am self taught and would really like to know if there are more consise formulas i can use. i am just working on the human hours at moment. next i need to make the cells for the meal and for daily fare
 

Forum statistics

Threads
1,086,033
Messages
5,387,396
Members
402,059
Latest member
wdyl121

Some videos you may like

This Week's Hot Topics

Top