HOURS of WORK MTH SHIFT CALENDAR: COMBO BOX without vastly increasing FILE SIZE?

[StephanRS]

Hello

Created WORK MONTHLY SHIFT HOURS CALENDAR: Calcs most things! nite/day hours/rates per shift, year summary Shifts/Miles per day/wk/mth/yr. Even calcs between start & finish time "&" night shift thresholds too!

It is a great spreadsheet, the only way I can think to improve it is with COMBO BOX selections in columns: F / G / J / K / L / M

However all previous attempts have created a file vastly bigger in file (for some unknown reasons), any concise formula edit "suggestions"?


Whilst you "contemplate" all that, or just love this spectacular spreadsheet in its own right, and after marveling it that u want to use it, then see instructions below photo:

Screenshot (211).png

START&FINISH, RATE, BREAKS, best to copy&paste from cell examples & edit, for consistency of cell formats.

Special Note:
Edit TIMES (HOURS: Start & Finish & BREAKS: 1,2,3) to edit enter number in 12 HOUR format:
Change HOUR number then AM/PM as required.
Example: 10am change to 13pm then:
10:00:00 AM to 01:00:00 PM (Hence number in 12HR then AM/PM)
It's a discreet detail but vastly important, as if edit differently total will be unrecognised, hence consistency works!

Simple to use, but needs some explaination to get familiar:
TIMES in START & FINISH (COLUMNS F & G) copy & paste edit to suit
PAY RATE in £HOUR (COLUMN J) copy & paste edit to suit
BREAKS in BREAK 1/BREAK 2/BREAK 3 (COLUMNS K , L & M) edit to suit copy & paste
MILES per shift is calced from BUSINESS CODE (COLUMN C) this is generated in tab CO (COLUMNS C & J)
NI/TAX/PENSION (COLUMNS Y, Z & AA) enter figures manual per wk or for per mth for TAX/NI/PENSION (CELLS F37, G37 & H37)

Summary TABS:
TOTAL = SHIFT day per day/wk/mth/year av/total
MILES = SHIFT mileage per wk/mth/year av/total
or for per mth for TAX/NI/PENSION (CELLS F37, G37 & H37)

Summary TABS:
TOTAL = SHIFT day per day/wk/mth/year av/total
MILES = SHIFT mileage per wk/mth/year av/total

[StephanRS]

Solved it, updated file attached, simple & made little difference to file size (8kb!), hooray!
Screenshot (221).png
INSERT > DEFINED NAME > LIST (COMBO tab: TIME & BREAK)
DATA > VALIDATION (Per cell, but just copy&past)
TIME: =IF($C2>0,TIME,F2) ROW2 example
BREAK: =IF($C2>0,BREAK,K2) ROW2 example

Super simple to do & easier to use, its all good!

[StephanRS]

UPDATE. Combo Box selection for hourly RATE added!
Screenshot (220).png

So COMBO BOX:

HOURS (COLUMNS F & G)
RATE (COLUMNS J)
BREAKS (COLUMNS K , L & M)

NOTES:
Combo entries of HOURS/BREAKS can be manually edited, say 10:00 to 10:30.

All other cells except Business Rows A-C (Copied from tab CO) are FORMULA calcs except: ni / tax /pensions deductions per wk/mth.

[StephanRS]

UPDATE! Combo Box selection added for BUSINESS! (Columns A/B/C): File attached btm of post
Screenshot (223).png

FORMULAS are DEFINED NAME & DATA VALIDATION:

COLUMN A:

DEFINED NAME
Col1List_EMP
=OFFSET(CO!$AH$2,0,0,COUNT(IF(CO!$AH$2:$AH$1000="","",1)),1)

DATA VALIDATION
=IF(AF2="",Col1List_EMP,A2)

COLUMN B:
DEFINED NAME
ColStart_EMP
=CO!$A$1

ColEnd_EMP
=CO!$A:$A

DATA VALIDATION
=IF(AF2="",OFFSET(ColStart_EMP,MATCH(A2,ColEnd_EMP,0)-1,1,COUNTIF(ColEnd_EMP,A2),1),B2)

COLUMN C:
DEFINED NAME
Col3Start_EMP
=CO!$B$1

DATA VALIDATION
=OFFSET(Col3Start_EMP,MATCH(A2,ColEnd_EMP,0)-1+MATCH(B2,OFFSET(ColStart_EMP,MATCH(A2,ColEnd_EMP,0)-1,1,COUNTIF(ColEnd_EMP,A2),1),0)-1,1,COUNTIF(OFFSET(ColStart_EMP,MATCH(A2,ColEnd_EMP,0)-1,1,COUNTIF(ColEnd_EMP,A2),1),B2),1)

[StephanRS]

Great Forum, answering my own questions!

Not sure why I'm registered.... ding ding... (Alarm style noise in background)

[StephanRS]

Newest version attached for use!

Now includes:
Combo Box selection of: Business/Hours/Breaks and calcs Night/Day Hours (Within Threshold Start/End) inc break subtract per Night/Day.

Attachment 754961