Tracker
HOME    TRACKER    Excel

# Schedule, Organizing

## I make our employee schedule at work, and so in Excel I have eight sheets for one workbook. (Employee schedule, then each day of the week) On each day is a table separating the shifts. Morning, Split and Night. I was wondering if there was a way for Excel to look at the schedule sheet, and then automatically place each employee and their shift on the following day sheets. (example: Joe has 2-10 on Monday, 10-6 on Thursday and Friday) So the Excel puts Joe underneath Split 2-10 for Monday, and Morning 10-6 on Thursday and Friday.

Related Forum Messages:
Organizing A List
I have attached a file that I need a little guidance on. The list of information on the left needs to be organized and put in the appropriate tables on the right. Each row represents one person and I need to know how many people from the list fall into each category. For example: The first row has a gender of "1" and the age is "46" and the status is "1". Therefore, this person falls into the category associated with "K11", I filled it in already. My list will vary in length from 20 rows to 20,000. I am not sure how to go about this and have been frustrated by failed attempts.

Organizing Worksheets In A Workbook
Is there a way to sort the worksheets in a workbook after they have already been entered. My clerk put 200+ worksheets in one workbook....out of order.

Code For Organizing A Worksheet By Rows: Not Running
Sub SplitOut()
Dim DataSH As Worksheet, OutSH As Worksheet
Set DataSH = Sheets("Input")
DataSH.Activate
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For Each ce In Range("D2:D" & lastrow)
Application.StatusBar = "Actioning " & ce.Row & " of " & lastrow.............

I am working on this spreadsheet that has a lot of information that will only get bigger as time goes on. I would like to organize the contents of one column into separate spreadsheets. So if I have "stacking" in one cell of that column, the whole row associated with that cell will go to a new spreadsheet labeled "stacking".

I have attached the sheet. I would like column D (Type_Code) to be organized by the words in that column. So I would have 5 or so worksheets labeled stacking, cartoner, wheel, inspection, etc. The data here is input by a machine.

Inserting Blank Row And Organizing Time-column
I'm a novice in VB and can't work out how to solve the following problem:

I have imported NMEA-data in text format from a GPS into Excel. This data is acquired in real-time at 10Hz, which borders what the GPS in capable of calculating. As a result the data isn't quite reliable enough - there are strings missing and some lines have been skipped by the GPS. This is a typical example of what sometimes happens: ...

Organizing Statistics (manipulate Team Statistics)
I want to manipulate team statistics and having a bit of trouble trying to figure out how to do it. I am relatively new to excel and am using Excel 2007.

I have attached the excel file for your reference (the same file).

What I need to do is first find out the team number using the table on first sheet named "Teams". User will enter team name on call B4 of sheet "Team entry" then in cell C4 there should be a formula to find the team number (is beside name on sheet "Teams) and displays it (on C4). Then on cell D4 of the same sheet "Team entry" there should be a formula that displays cell A1 (shows a statistic for that team) of the team sheet. Each team has its own stat sheet named by its team number (easier for me to keep track). So basically D4 should show cell A1 of the sheet that shows stats of the team entered. In addition cell E4 of "Team entry" should display stat2 (of the sheet "stat2")for the team entered.

Scanning Schedule
The table posted above is the schedule for my employee. I need an input box which asks for a date. When the user supplies date, it should look for people who are scheduled for that particular date and who are on leave and put the result in the next worksheet at the last occupied row.

For instance, if I supply the date as 2nd Dec then the next worksheet should be filled with @ row # 2 (Row1 has headings)

EmployeeEmp IDSchedule
A123409:00 PM - 06:00 AM
B123511:30 PM - 08:30 AM
C123602:30 AM - 11:30 AM
E123811:30 PM - 08:30 AM
G124009:00 PM - 06:00 AM

Then if I select 3rd Dec, the next worksheet should be filled with the following data starting fom row number 7 (just below the above data)
A123409:00 PM - 06:00 AM
B123511:30 PM - 08:30 AM
D123705:30 AM - 03:00 PM
G124009:00 PM - 06:00 AM

Creating A Schedule
I am Cuttently creating a schedule in excel for work. the schedule will look like this:

Name.........................Shift
Andrew.......................6-3
Jonathon....................12-7
Kim............................9-5

etc etc

the thing is though this is simple enough but i am trying to use a bar chart to help me show how many people i will have on shift at anytime e.g ...

How To Write A Schedule
Excel is a new program for me sorry to say it but i have been try to figure out how to write a schedule on it and tally the hours for each employee i have them set up in rows right now and i hope i can keep it that way but i am tired of add hours up for each employee every week i'm sure it is really easy but can some one let me in no the little secret

Creating A Schedule ..
which is row D on my excel sheet. I need to fill into another form who works shift D, shift E and shift N. Is there a formula I can use to do this? I have given a small sample of my schedule below.

AB CD 252627ShaunD DDNicoleD EDEmilyE DECathyENEJohnNEN

Schedule Template
We have huge restrictions on hours, and to avoid inputting, then revising the schedule into my companies scheduling module, then having to change it again when the boss looks it over, it's just confusing.

Anyway, I'll get to the point, I want to add the hours up in a row of cells for the week sunday through saturday. Like so:

1100-2100 | 1100-2100 | OFF | 1100-2100 etc... I want it to add these cells hours up and have the total end up in the 8th cell.

Is this possible? I am a new excel user, or at least, setting up my own sheet.

Weekly Schedule
Have a basic weekly schedule for 300 employees. Need to be able to identify the last day off in previous week so the following week isn't scheduled to work on the 6th or 7th day without a day off. For example, if schedule is Mon - Sun and employee #1 has Mon & Tues off this week, next week Mon would be the 6th day so I wouldn't schedule that person on Monday the following week. Calculate last column "Next Wk Mon" instead of manually figuring it out... Need to be able to identify last day off, count the days from that point to the next monday. It becomes difficult when days off are split...

MonTueWedThuFriSatSunNext Wk
23-Feb24-Feb25-Feb26-Feb27-Feb28-Feb1-MarMon
Emp 1OffOff6
Emp 2OffOff5
Emp 3OffOff4
Emp 4OffOff3
Emp 5OffOff2
Emp 6OffOff1
Emp 7OffOff1
Emp 8OffOff4
Emp 9OffOff2

Reset The Schedule..
Is there a way to take the values between C2 & D2 and have them automatically post in column F ?

Im having trouble with getting a macro to run at a specific time and day (Every Thursday at 7PM).

Here's what I have done so far..

2. created a class module and place the code below into it.

In testing the workbook opened but it asked if i wanted to enable or disable the macros - how do I get around this? And it does seem to kick off the macro (create_all).

Private Sub Workbook_Open()
If Hour(Now) = 1130 And Weekday(Now, vbMonday) < 7 Then
' TheNameOfYourCurrentProcedureHere
create_all
If Workbooks.Count = 1 Then
Me.Save
Application.Quit
Else
Me.Close True 'save the changes and close the workbook
End If
End If
End Sub

Maintenance Schedule Checklist
I am trying setup a maintenance schedule checklist for a large list of items. Each item has a cycle in weeks for the checklist. I set up a nested if statement to check if the item is overdue or done based on the cycle and the current date. The if statement checks when the last time the item was done or overdue and populates an "X" for the next due date. It works fine as long as the cycle is 4 or less. After that there are too many if statements and I get an error. I'd rather not have to use a macro to get this to work. Any ideas?

Schedule A Macro To Run
I am trying to schedule a macro to run at 11:45 pm every time. I use system scheduler to open the excel file and when I use this command to run the macro nothing happens. The workbook opens fine but the macro does not run. I only wants to run the macro once day even if I open the workbook during the day I do not want it to run.

Accounting Prepayment Schedule
I'm trying to automate the calculation of my prepayment schedule. The linked image http://img402.imageshack.us/img402/2...mentqueryp.jpg shows the basic layout of how it will look. The yellow cells are going to be the only input cells but i'm unsure what formula will achieve the desired result i'm looking for across the remainder of the spreadsheet. Essentially I need the formula to look at the period (start and end dates) the invoice covers and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.

For example in the car park rent line the figure of £8,000 in July is derived from the fact that that is how much hasn't effectively been incurred yet as it relates to the remainder of the invoiced period (Aug - Mar) and is calculated as 8/12 x £12000.

I hope you understand what i'm requesting, if not, let me know and i'll try to provide a better explanation.

Schedule The Hours Are Different Than The Numerical Value
I have an excel document and I don't know which formulas to use. Here is how the document is laid out, it's an employee schedule.

The row includes A: Name B: Sunday C: Monday ... J: Total

Columns below include the employee names. What I am doing is filling out the employee schedule and I need excel to calculate scheduled hours. My problem is, when I make the schedule the hours are different than the numerical value given by excel. For example, Sunday I have an employee scheduled from 8-4. That's 8 AM until 4 PM; or a total time of 8 hours. Excel would read that as 8 (minus) 4, rendering the results useless. Once I figure out how to calculate hours by such input, I'll need to make totals which won't be as difficult.

Also, if there is a way to set the value of the word "Close" to 12:15 AM that would be extremely beneficial as well.

Weekly Employee Schedule
I'm trying to create a weekly employee schedule using Excel 2007 and have several issues but will start with one at a time till we get them all resolved.

I put in my start hour '6' in cell 'B7' and get this: 1/5/1900 12:00:00 AM.

I would like for to display 6AM.

I figure if I get his cell fomatted that I can get the other times correct then go on to the next issue.

Work Schedule Timesheet
I have made a work schedule for my local business and have set up a series of formulas that will fill out time cards that I could print out directly onto the paper time cards. The formulas that I have work except that if there are two subsequent entries that later will not return a value and result in an error.

If you could take a look at it that would be awesome. To use it you just need to type a name into the name column and a work time into the time column for that day. then in the other sheets( one for each worker ) it will set up the time card. The the error happens on Thursday, when Bob has an entry right after Fred. Then on Bobs sheet it gives me a #N/A.

Time Sheet Schedule
I need to do is calculate the hours between two clock times minus 30 minutes.

Example:
Cell A1 - 4:00am
Cell B1 - 16:30
I need Cell C1 to say 12 (hours)

Also:

Cell A2 - 17:30
Cell B2 - 02:00am
I need cell c2 to say 8 (hours)

Networkdays In Production Schedule
I have attahced an example of what I need to do. I need to project due dates for each of our 8 departments based on a ship date of the final product. Each of the 8 dept. have a number of operations (ops) and given number of hours for each op. These alocated hours change on every job plus I have 2 outside operations that may or maynot be added to the mix. I don't have a clue on where to even start with this formula.

Validating Times In A Schedule
I have been trying to achieve the following with formulas but have not been successful. I am making a simple schedule, and I am trying to validate the cells so that you can not schedule someone for multiple tasks during the same time range. In the example that I attached, Harry is diswashing from 1:00 - 4:00 but is also scheduled to paint from 2:00 - 5:00 (this should not be allowed). How can I achieve this with code?

Tab On The Sheet That Looks Up Name And Compares Against A Schedule
I am currently trying to take a list of people who have log in times at work however if they log out then back in the result is what you see below.

What I am looking to do is match the find the name then find the earliest time (login Column C) and then the last or latest time (log out Column D)

A B C D
John Smith64676076:02 AM6:13 AM
John Smith64676077:25 AM11:27 AM
John Smith64676212:08 PM4:01 PM
John Smith64676076:13 AM7:25 AM

Would like a formula as I have another Tab on the sheet that looks up name and compares against a schedule to see if the person was late or left early.

Create Meeting Schedule Sheet
I have three columns. the first column is a list of buyers (19), then second column is a list of suppliers (27) and the three column is the time. each supplier has ranked the buyers and the order they want to meet them.

every meeting is for 20 minutes e.g 1st meeting at 8am then next at 820am.

i can do all of it manually but the schedule becomes too long.

is there a way to make the schedule in a way that it is easily readable by anyone and if I need to add anything, it should not be an issue.

Schedule Report - To Be Generated For Each Individuals
I have a schedule sheet (on week days what assignment is be to taken in concerned period).

I need a report based on the same. i.e. I need to generate a report for each individual.

I have attached a sample file, in which I explained the requirements.

Employee Time Schedule, Calculations
I have tortured the cells into producing the output I want...

Namely time(s) in and out, with the hourly totals - subtracting 1/2 hour
for shifts over 6 hours.

I however have a couple of questions about glitches the approach I am using give me.... I will outline as best I can: wish I could upload a sample...

1) the user inputs (in the time format) the starting and ending times for the individual ie. 9 A = 9:00 AM ... 1:30 P = 1:30 PM simple

2) after the user inputs the total formula for each day is calculated:

=IF(D3="","",(IF((D3-C3)>.25,((D3-C3)-0.020833),D3-C3)))

That leaves the cell null if there is no time in the day...
If there is time, checks to see if more than 6 hours...
If so, deducts 1/2 hour, else does the difference calculation
This does work, but is it the elegant or simple way? recommend?

3) At the bottom of each day totals are calculated by this :

=Text((SUM(E5:E36)),"[H]:mm")

This also works to sum the individual totals to the bottom
But is this the best way to work with the times?

4) the individual then inputs the Actual Hours worked by the team...
And the sheet does a variance calculation between actual hours
Worked vs. Scheduled hours...

=TEXT((D38-D39),"[H]:mm")

Now this is a problem...cannot do negative times....need help!

5) Am having a problem with totaling the individuals weekly total using ...

=TEXT((E3+I3),"[H]:mm")

Macro To Calculate Billing Schedule
I have just been passed a new VBA project and there is one component I would like help with.

Column J lists a start date
Column K lists an end date
Column N lists a dollar amount.
Columns P through the last column has headers of month-year in text form(for example May-08)

The user enters contract information in the spreadsheet including the above cells. What they want to automate is once the user enters the contract information on a given row, she runs a macro to determine the billing schedule by calculating how many months there are between the start and end dates, divide the dollar amount in column N by that number and then prompt the user to enter what month the first payment is due, once that is entered, the macro should place the payment amount in that column and all applicable subsequent columns.

For example,
Column J = 6/1/2008
Column K = 9/1/2008
Column N = 3,000
The macro should calculate 3 months, the monthly payment amount is \$1000.
A starting month prompt appears and the user enters July-08. The macro then places 1,000 in the columns for July-08, August-08 and September-08.

Trying To Design A Creche Enrolment Schedule
im trying to make a few things work at different stages.

1. Im TRYING to create a not so simple programme with Excel for My Wifes Creche - She would like to have a programme that, when she gets a phone call from a parent to check availabilty in lets say - 3 months time, it reports back with a number of availabiltys for that date in a specific room.

2. Rooms are broken in 4 categories ( up-to 12 mths old "Baby room" 12 mths to 2 yrs "Wobblers room" 2-4 yrs "Toddlers room and finaly Playschool,)

3.So far what i have done is this =IF(G2<\$A\$1,"",IF(G2<\$A\$1039,"Baby",IF(G2<\$A\$1404,"Wobbler", IF(G2<\$A\$1770,"Toddler",IF(G2<\$A\$2290,"Playschool",
which gives me a room from inputting the Date of Birth.

4. ive then used the form button to give me the option to add edit and delete entries from the headings (First name Last name Date of Birth Room -but this will show once DOB is entered.

5. i have tried to run the =COUNTIF(A2:A7,"Baby") to try and get the total baby spaces taken up at a given date however this wont work for me and isnt giving any error message, just a blank cell.

Subtracting Hours In A Work Schedule
I am working on a monthly employee work schedule that has 2 stores. All employees work at both stores and the boss wants 2 separate schedules.

I have attached what I have started, which at the moment I am stuck.

I would like to have the Row 5 total hours to always reflect (subtract) a 30 minute lunch. I know it would be easier to have another column, but the boss wants it this way,

I also need the total hours in column H to reflect the total of each day (with the subtracted lunch break). In this column I am having the figures turn red if over 40 hours and green if it under 40 hours.

With having 2 stores with the employees working at both, I need to somehow have the hours of the employees add up from one store to the other (a running total, if you will).

Lastly, if I have an employee not scheduled for a day or need to put other data such as, VAC, HOL or blank cell etc in a cell, how can I get this to work and not have the formula go nuts when it is not in a time format.

Real Estate Commission Schedule
I receive a certain percentage of my broker's commission based on what type of house sale occurs. When one of my listings sell I receive the commission in A2:A7. When I sell a house to Company A I receive the commisions from B2:B7, company B C2:C7, and company C D2:D7.

My own personal commission percentages increase based on the income schedule E2:F7. For example, once I have earned \$8137, my percentages for sales all jump to Row 3.

I have set up a chart below the commission schedule for each individual sale to calculate the commission for each type of sale. Each "x" represent a sale for each category (LISTING, COMP A, COMP B, COMP C). The broker's commission is always 3.5% of the total sales price. My commission will be a certain percentage of the broker's commission based on the scale above.

Schedule Workbook Open Automatically
Is there anyway I can schedule an excel workbook to open up at a certain time; I have some code attached to the Workbook Open function; so when the workbook is opened the code will run; but I need the workbook to automatically open at say 17:00 everyday so the code can run.

Booking Schedule For Sport Teams
I am creating an hourly schedule (for a calendar year) that shows rental space occupied by 4 different groups (baseball, softball, soccer and football). I have created worksheets for each group and am entering proper names. I want to be able to search the worksheets for ANY text and return a value to a master sheet based on the group they belong to. If the cell is empty I want it to remain empty in the master sheet. Example: Ed Jones is entered in sheet 1, cell a15, and will show as "Baseball" on the master sheet cell a15, Mary Smith is entered in sheet 2, cell b16, and will show as Softball on the master sheet, cell b16, etc. The sheets are divided out into sports fields that they can rent. Multiple sports can rent the same fields so it is important that my master sheet show who is renting the field that day so it can be set up properly and they don't get double booked.

Schedule Macros To Run While Workbook Is Closed
I'm trying to look at options for scheduling macros to run at a particular time. Ideally I would like to run one at 3:00 am during every week night. From what I've looked into, I can run the OnTime procedure and it could schedule this to run each night. Does anyone know if this is correct, and if there are any other options avaliable to me?

Also, does anyone know if this can run automatically if Excel is closed, or does Excel have to be open? Does the workbook have to be open?

Create A Graduating Schedule To Figure Fees
I am using Excel 2007. I want to create a graduating schedule to figure fees. For example, \$10 per thousand for the first \$500,000. \$13 for the next \$2,000,000, etc. I have tried to understand the IF function but am not sure if this is the correct one to use.

Apply A VLOOKUP Function To Schedule Sheet
I'm trying to apply a VLOOKUP function to my schedule sheet. Attached to this post is a copy of the sheet.

The shifts that I'm entering in B7:AC7 should be looked up in the table located at K21:Q25. I'd like the looked up value to display in B8:AC8.

Find, Compare And Assign A Value For Schedule Format.
I have a schedule database the one I need to transform some cell values in time format. The thing is that I have the format as follows, and I was trying to set a macro to convert every "*" in 15 min value without succcess.

Example
Associate below works as folllows:
Sat 3:30 - 8:30 Mon: 4:00 - 8:00
Sun 2:30 - 5:45

*(start) represents 0:15 min value.
I need to transform the *'s and sum the time format and place the value below the START and STOP column.-

Note: (disregard dots)

Agent: Julia Roberts
Day Start Stop 00:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00
Sat.............................................** **** **** *** **** **
Sun.....................................** *** **** ***
Mon ...............................................**** **** **** ****
etc

Employee Schedule Auto Complete In New Worksheet
This should be really easy, but for some reason I can't figure out how to make this work. I'm managing a resteraunt, and build my schedule by department using a drop down menu to insert my employee. I'd like to be able to automatically resort the data into a new worksheet organized by employee name. please see attachment.

Schedule VBA Script To Email Cell Value
I have been doing a lot of research on macros and vba scripts recently. I essentially want to email the value of a cell which I know is possible with a macro. To make things a little more difficult, I want to schedule this using Windows Task Scheduler. Here is my exact situation; I take care of a bill for my house. If my roommates carry a balance, I want them to be emailed once a week of how much they owe me. I want this macro to be independent of my excel file, meaning I don't want to do run every time I open the file or anything. I only want it sent once a week. If anyone could please advise on how to develop this script, I would greatly appreciate it. Please let me know if you need additional info.

Current system:
Windows Vista
Office 2007
Thunderbird email

Autorun Script/macro Sql Schedule
I have a script that collects data from a mySql database. Data in the database is moved with certain intervals to a bigger database (which takes longer time to run quaries against. So my thougt is to make to user able(in my excell apllication) able to schedule autorun of diffrent scripts. Do you guys know any way to schedule autorun?

Custom Date Series Class Schedule
I would like a simple way to make a single column whose rows are sequenced Mon, Wed, Mon, Wed, ... and have the correct dates. For example:

Mon 2/11
Wed 2/13
Mon 2/18
Wed 2/20
Mon 2/25
etc.

Cell A1 could have Mon 2/11, Cell A2 would have Wed 2/13, and so on. (20 to 50 rows)
The ordinary Fill Series when applied to Mon Wed will continue with Fri Sun Tue Thu and so on, or it will return Mon Wed Mon Wed Mon ... with the wrong dates. Also, the solution should be applicable for a Tue, Thu(TTh); or Mon, Wed, Fri(MWF); or Mon, Tue, Wed, Thu (MTWTh) series.

Open Outlook And Schedule Meeting Via Command Button
I am trying to have a Command Button launch Outlook and attempt to schedule an Appointment.

The person would enter the date on to the cell below and the time as well. Additional I would also want it to grab email addresses from the Recipients box and place them on to the TO field....

Work Schedule: Insert The Numbers 1-6 Into The Spreadsheet Throughout The 14 Days For Each Employee
I developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not.

My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator

Amortization Schedule: Auto Update Based On Loan Period & Number Of Payments Per Year
I have uploaded a sample amortization schedule.

1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.

2. Also, if a value is entered in column E, then i require the whole table to update as well.

Changing Date/time To Run A 12 Hour Production Schedule, And Not 24 Hour
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).

The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.

The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.

My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.

Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.

Of course if demand exceeds the allotted time we put on overtime.

Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?

And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.

I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.

Counting The Days Worked And Days Remaining On A Schedule Automatically
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.