SOLVED

Excel Shift Work Calendar Template editing

%3CLINGO-SUB%20id%3D%22lingo-sub-2045482%22%20slang%3D%22en-US%22%3EExcel%20Shift%20Work%20Calendar%20Template%20editing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2045482%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20use%20the%20Shift%20Work%20Calendar%20Template%20in%20Excel%2C%20but%20it%20is%20only%20built%20for%203%20jobs.%26nbsp%3B%20I%20need%20to%20edit%20for%3CSTRONG%3E%206%20jobs%3C%2FSTRONG%3E%2C%20but%20can't%20figure%20out%20how%20to%20do%20it.%26nbsp%3B%20I'm%20sure%20it's%20a%20simple%20solution%2C%20but%20I'm%20new%20to%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20assist%2C%20it%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20template%20I%20would%20like%20to%20use.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2045482%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2045536%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Shift%20Work%20Calendar%20Template%20editing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2045536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923192%22%20target%3D%22_blank%22%3E%40NIcholas_Dube%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20such%20template%20it's%20a%20lot%20of%20manual%20work%3C%2FP%3E%0A%3CP%3E-%20in%20Shift%20and%20Jobs%20copy%20one%20of%20jobs%20(e.g.%20%233)%20column%20into%203%20new%20to%20the%20right%3C%2FP%3E%0A%3CP%3E-%20define%20job%20data%20as%20needed%3C%2FP%3E%0A%3CP%3E-%20check%20one%20by%20one%20which%20names%20are%20applied%20for%20the%20cells%20for%20job3%20and%20apply%20similar%20for%20job4%2C%20etc.%26nbsp%3B%20Copy%2Fpaste%20such%20names%20using%20name%20box%20on%20top%20left%20from%20the%20grid%2C%20just%20replace%20within%20them%20Job3%20on%20Job4%2C%20etc.%3C%2FP%3E%0A%3CP%3E-%20at%20the%20bottom%20of%20each%20month%20insert%203%20empty%20rows%3C%2FP%3E%0A%3CP%3E-%20copy%2Fpaste%20Job3%20formula%20for%20the%20first%20date%20into%20first%20date%20for%20Job4%3C%2FP%3E%0A%3CP%3E-%20replace%20within%20formula%20names%20with%20Job3%20on%20names%20with%20Job4%20and%20reference%20on%20cell%20C6%20on%20reference%20on%20cell%20C6%3C%2FP%3E%0A%3CP%3E-%20drag%20this%20cell%20to%20the%20right%20till%20end%3C%2FP%3E%0A%3CP%3E-%20repeat%20for%20Job5%2C%20Job6%3C%2FP%3E%0A%3CP%3E-%20repeat%20above%20for%20each%20month%3C%2FP%3E%0A%3CP%3E-%20open%20Conditional%20Formatting%20box%20and%20adjust%20Apply%20To%20ranges%3B%20use%20F2%20to%20edit%20within%20bar%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20attached%20one%20job%20for%20one%20month%20is%20added%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I would like to use the Shift Work Calendar Template in Excel, but it is only built for 3 jobs.  I need to edit for 6 jobs, but can't figure out how to do it.  I'm sure it's a simple solution, but I'm new to this.

 

If anyone can assist, it would be greatly appreciated.

 

I have attached the template I would like to use.

3 Replies
best response confirmed by NIcholas_Dube (New Contributor)
Solution

@NIcholas_Dube 

With such template it's a lot of manual work

- in Shift and Jobs copy one of jobs (e.g. #3) column into 3 new to the right

- define job data as needed

- check one by one which names are applied for the cells for job3 and apply similar for job4, etc.  Copy/paste such names using name box on top left from the grid, just replace within them Job3 on Job4, etc.

- at the bottom of each month insert 3 empty rows

- copy/paste Job3 formula for the first date into first date for Job4

- replace within formula names with Job3 on names with Job4 and reference on cell C6 on reference on cell C6

- drag this cell to the right till end

- repeat for Job5, Job6

- repeat above for each month

- open Conditional Formatting box and adjust Apply To ranges; use F2 to edit within bar

 

In attached one job for one month is added

Thanks for the help