I need help with formulas and formatting to create a complex employee schedule maker layout


I have limited knowledge of terms and nomenclature regarding excel. It's best if I describe worksheets function. I need help in formulas and specific directions in order , after lined square areas and format merge process is completed. If it is even possible for all actions to work if they can't all work ill take what i can get.

2) line bordered, 2 column 4 row cell section areas. Formatted to show two, 2 column cells merged at row in top two rows and two 2 row cells merged at 1 column left and right. 1 area designates (non-print) and other area (print).

(Non-print) enter top row as 4 numbers to represent for employee (in time). Second from top is for (out time.)

(Print)top row displays (in time) as actual time am/pm. Second from top is (out time)

(N/p)bottom left cell displays amount of hours between in and out time. Bottom right is an input cell.

(P)bottom left displays L if (n/p) bottom left value is greater than 5 and 0 if not. Bottom right displays -30 if - entered in (n/p) bottom right and L30 if entered +.

In both  areas, they're are 7 of these square areas left/right and 12 areas top/bottm. Above and below each square are 2 column cells at 4 rows formatted merged at bottom and top is 2 columns 1 row merged cell sections

(N/p) two cells assigned to input time for store open and store close. If all numbers in range of set input areas for open/close on cells for time in out are filled then 2 x 4 cell merged section below sections for each day fill green displays proceed in more than 1 hour total is missing from range then cell displays red with stop message. In top two cells of all squares left/right fil cell green if less than 25 hours accumulated for week, if more than 25 but less than 27 cells fill yellow greater than 28 equals red.

I hope this makes sense, thank you for all of your help.



Selected Answer

A good way to start a project like this is to create the worksheet (or worksheets) without any functionality, just to show what goes where. Name all the sheets with intelligent names, name all the columns with descriptive captions. Give a suitable width to all columns. Format all the cells to suit the content they are supposed to display.

The second step is to invent the work flow. For example,

  1. User enters the date
  2. User enters the description
  3. User enters an amount
  4. System makes the amount appear in another column (on another sheet)
  5. etc.etc.

This method leads to a single question. In my example it is how to make the amount appear in another column or on another sheet. This question can be researched or it can be asked.

If you decide to ask such a question here you would best post your worksheet, point to the cell where you want your user to enter the amount and to the cell where you want it to also appear. For this kind of question you will receive a quick answer and you will be able to continue your work until you come to the next question.

Your above post doesn't mee the criteria. No workbook is posted. No worksheet is made available. No cell is defined. A large number of potential questions are mentioned but none of them precisely enough to enable an answer.

This forum is for teaching Excel, one question at a time. In order to find answers here you will have to find the questions yourself. The title you gave to your "question" is "I need help". That is not a question.



This is exactly what I needed to understand, thank so very much for your help.
KEBinAZ (rep: 4) Sep 16, '17 at 12:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login