Verifying Employee Shift Coverage

Oct 6, 2009

I would like to create a formula that would verify that specific work shifts have been covered each day. The spreadsheet has already been created by someone else, so I am hoping not to recreate the wheel.

The goal is to make sure that all desired shifts are covered with a result in the last cell of the column that would indicate "covered", "not covered" or even a true or false statement.
As an example, an 8am shift is needed. The choices are 8A or 8ALEAD and only one of these is needed for each day.

Can something be set up with conditional formatting from a master list of required shifts.
such as: 7OR, 730*(for just 730 and 730LEAD), 11A, etc.

This is just a snippet of what the schedule looks like: ...

View 14 Replies


ADVERTISEMENT

Formula Not Reading When Employee Shift Changes

Jan 24, 2014

I need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.

i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.

I've attached a sample workbook to show.

MDT Report December 201322(FILEminimizer).xlsx

View 2 Replies View Related

Employee Shift Roster Template

Nov 19, 2008

Any non-commercial roster template that would suit 7 day/24 hour/4 person shifts?

Currently we use a totally manual spreadsheet which looks like a Rubik's Cube and does not offer any form of automation in order to speed up entry adn ensure no doubling-up or missing shifts.

View 9 Replies View Related

24X7x365 Employee Shift Scheduling?

May 4, 2013

I'm trying to come up with an Employee Shift Scheduling template. I have 6 employees total with 3 different shifts (day, swing, graveyard). I would like to not do a rotating shift. I'm trying to figure out if a 5X8 shift is better or 4X10 and I would like to come up with a weekly schedule where I can see what shift everyone is working and were I have double coverage.

View 1 Replies View Related

Verifying Text Entries

Jul 18, 2009

I was given two files from an old DOS pos system to make a current mailing list with.

The first file is a list of all the customers from this company(full list), the 2nd is all the customers active within the previous year(active list). I don't have any access to any information other than what is on the 2 lists I was given and I need to use the active list to make sure that only the current customers get sent anything. Not normally a big issue, if it weren't how these lists were formatted when given to me. The full list is a mailing list that was formatted to run labels from a dot matrix printer and imports to excel in the following manner,

John & Jane Doe
1234 NE Road St
Someplace, OR 97056

The active list is formatted like:

Doe | John & Jane

Because there is no address information on the active list I can't just generate a full mailing list. I am able to seperate the values from the full list by referencing cells onto another worksheet (it seperates as 'name' 'address' 'city/state/zip') but I need to find a true/false formula, or something similar, that will tell me which names on the full list are also on the active list. I can do it the other way around, and check to see which names from the active list are on the full list (all of them) but because of how the values are seperated on each worksheet I can't seem to figure out how to make this work.

View 9 Replies View Related

How To Calculate Inventory Coverage

Apr 11, 2014

1/A
B
C
D
E
F
G
H
I

[Code]...

I am trying to calculate the inventory coverage. In C3, I have the inventory amount as of january-end, which is 47,6 M. I want to know how many days of inventory I am carrying, according to the later months cogs figures. In this example, I calculated it as 110 days of inventory. is there a single formula I can use, to calculate the inventory coverage for january, and copy it accross the all columns?

View 5 Replies View Related

Verifying A List Of Names And Locations?

Aug 21, 2014

I have 1 list with two columns, where column A is the location (a 3 digit code), and column B is the name (a 4 digit code).

I have a second list with two columns, again column A is the location, and column B is the name.

I want to make sure the locations and names in the second list match what I have in the first list. If they don't match, I somehow want to flag this in a third column.

View 4 Replies View Related

Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)

Dec 1, 2009

I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

View 3 Replies View Related

Calculate Pay For Shift Work With Different Rates Based On Shift Hours

Apr 11, 2008

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

View 8 Replies View Related

Verifying Multiple Text Boxes Are Not Empty

Nov 13, 2009

I have many text boxes on a form and if any of them are empty then I want a msgbox to popup and exit sub. I know how to do the following

If txtExample1 is "" then msgbox and exit sub
if txt....2
if txt...3

Is there anyway to group the text boxes to test for empty so you don't have to list 20 textboxes

View 3 Replies View Related

Excel 2003 :: VBA Project Absent And Coverage

Aug 30, 2012

I'm a basic user of excel 2003. I recorded a macro that copy a teacher absent schedule to a daily cover schedule as follow:

Sub ABS_M1()
'
' ABS_M1 Macro
' Absent teacher monday 8/29/2012 by Oscar
' Daily cover schedule
'
Range("A65:J67").Select
Selection.Copy
Sheets("Covers").Select
Range("B5").Select
ActiveSheet.Paste

End Sub

Using the same VBA: What function should I use to make the Sub ABS_M1 move down 4 rows to a new range on the cover sheet if the first Range ("B5") is already used and so on?

View 4 Replies View Related

Precision, Or My Maths (Each Water Plant Can Provide A Certain % Water Coverage To All Properties)

May 15, 2009

I have a number of houses & a number of water plants (as in water processing plants, not the green kind!) Each water plant can provide a certain % water coverage to all properties. 'All Properties' is classed as the number of houses + the number of water plants. The 'coverage' as a percentage is given by the formula:

View 5 Replies View Related

Start Shift And An End Shift

Nov 18, 2009

I have a Start Shift and an End Shift time,

Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs

I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.

View 12 Replies View Related

Getting Name From Employee#

Sep 29, 2009

This is what I am looking to do and I am having an issue with it.

worksheet "beeble"

Column B has all the employee numbers listed from B3 to B100
Column C needs to have the emp name put in to them based on the emp number.

worksheet "weeble"

This sheet has the list of employees with their Emp#

A2 down to A99 is the number B2 on down is the name that belongs to the emp #

At issue is sheet "beeble" changes day to day depending who is scheduled to work in a certian area, otherwise this would be quite easy.. It is very easy for us to put the emp# in instead of the emp-name, so that is why wwe would need to be able to pull this information from the other sheet.

This may end up being very easy, but it is beyond me, and I cannot find what I need from a book, as I spent last night on here searching and reading through a few of my books..

View 13 Replies View Related

Employee Number

Sep 3, 2007

I have a list of employees that i need to simplify into groups as listed below

Employee No's
1-19
20-50
50-199
200-499
500+

In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:

Employee List
4
28
35
46
503
376
2000

Employee No's
1-19 - 1
20-50 - 1
50-199 - 3
200-499 - 1
500+ - 2

View 11 Replies View Related

Name Appears Instead Of The Employee ID

Sep 11, 2007

SEE ATTACHED FILE

All,

I have a spreadsheet that lists each employees hours for the last 4 pay periods...each is in it's own row I am trying to find out their average.

I am using the subtotal function to average their hours and that works fine. BUT...my boss doesn't want to see 4 rows for each person. He just wants to see one row for each person and when I collapse the rows, it only shows the employee's ID, not the name (because that's what I told the subtotal function to do...add subtotal after every change in employee ID).

name appears instead of the employee ID? That's useless to him because he hasn't memorized all the Employee's IDs.

View 13 Replies View Related

Projects Per Employee

Apr 17, 2008

I was given a task of calculating bonus for number of projects per employee. The maximum number of projects per employee is 30 and they have completed different number of projects. Data is as follows:

Column A - Name
Column B - Date
Column C - Project

Employee name repeats one row per project and project repeats as they are working with it.

I need to list individual employee names in column D and the number of projects each employee has done in column E. A project can be saved many times thus creating many rows for that same employee. Do you think it is possible to accomplish this. One formula for column D and one formula for column E. If needed I can attach an example file or take a screen shot of it.

View 9 Replies View Related

Ranking The Employee

Feb 2, 2009

i have two col A and B

In col A name of the employee
In col B there production for the Month

I need in c col to rank them that according to there production for the month like 1,2,3, etc.

View 9 Replies View Related

Employee's Project Report

Jan 26, 2010

I have created a spreadsheet in Excel where it gives me a report of how many hours my employees do per week and it seperate them in different categories.

What I would like to do is find a way to match the cell's description with the amount of total hours that were spend on certain project.

So here is the scenario.

In my department there 6 employees that are assigned to work on certain projects in daily basis. These projects are called CRs and to identify them I've added a number after them.
So we will have CR0001, CR0002, ect. These are unique projects.More that one employee that could woork at the same project as well on other sections but I am only interested on the projects.

To help you understand what I am trying to do I will give you an example.

John is working on project CR0005, CR0006, CR0001. He has been working on them three projects for the last week.

Here is the summary of the hours:

- 5 hours on CR0005
- 10 hours on CR0006
- 5 hours on CR0001

This information is inputed in Excel spreadsheet Week 1 .....

View 12 Replies View Related

Employee Retirement Plan

Oct 27, 2007

how to use formula in the monthly contribution field to calculate 7% of pay (salary)

Lenght of Total pay Monthly contribution cummulative
Service retirement value
3.6 $528.94

Please one more thing. How do i use FV Function, monthly contribution, and the lenght of service field to to determine the current value of the employees retirement palan.

View 10 Replies View Related

Employee Timesheet With Overtime

Nov 2, 2008

i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.

View 2 Replies View Related

Weekly Employee Schedule

Sep 16, 2008

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.

View 10 Replies View Related

Tracking My Employee's History Of Changes.

Apr 1, 2009

I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel.
Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.

The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters $40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say $16.75.

View 9 Replies View Related

Employee Time Sheet

Apr 8, 2009

I have problem to make an excel evidence which will track total working hours of employee during the month, how many times employee was late for work and how many times employee was on lunch break longer than 30 minutes.

And I made table which count all these things but problem is Saturday because on that day start work is 9::00 AM and all other days is 8:00
Formula for counting how many times employee was late is:

COUNTIF(D8:AC8,">0.336805555555555555555555555555")
where D8:AC8 is range where are all start work times in month and 0.336805555555555555555555555555 is 8:00 AM (serial number which is equivalen of 8:00 AM)
How to improve this formula to count properly because now it counts as a late when employee start work on Saturday on 9:00 AM (and that shouldnt count as a late)
[IMG]file:///C:/DOCUME%7E1/Nesa/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]

View 9 Replies View Related

Employee Scheduling Using Solver

Sep 26, 2006

I have a scheduling problem that I'm trying to get my head around. Im sure that some kind person can give me some ideas on how to get this started. It may be extremely easy, but I just cant get it off the ground. Basically I have 15 doctors that work for me - 9 full time (38hrs/week) and 6 part time (15+hrs/week) in a general practice. We are open 7 days a week

Open Close
Monday8:3019:00
Tuesday8:3019:00
Wed8:3019:00
Thur8:3021:00
Fri8:3019:00
Sat9:0018:00
Sun9:0018:00

Doctors have 5 days on and then 2 days off.

The below is the proposed daily schedule and the doctor requirements. % of booked patients for the times and % of random patients for the times are included as well as average waiting times.
Booked Random Waiting Doctors
8:30 - 10:00Morning Hours55%45%30min5-7
10:00 - 13:00Increasing80%20%45min7-8
13:00 - 18:00Busy 100%01.5hr8+
18:00 - 19:00Decreasing70%30%1hr6-7
19:00 - 21:00Night 40%60%30min5+

There are 3 types of patient visits
ShortDoubleTriple
%0.810.140.05
Minutes153045

58% of patients choose their doctor and 42% come in off the street and will take the first available doctor.

and finally - doctors cant work for more than 4 hours without a break. brek details are below
HoursBreak
<4hrs0
4 - 5hrs10min
5 - 8hrs2*10 mins - 45min lunch
8+2 * 10min 2*45min lunch

I need to create a timetable for the doctors which optimises their time based on the above details. I also need to design a data sheet for reception which will maximise the number of patients seen. Finally I need to calculate what the maximum number of patients the centre can see on an average day say monday.

View 9 Replies View Related

Calculate Each Employee Work Hours

Apr 11, 2013

Just like if I put the employee's number and work time for each day,

The excel will accumulate the hours automatically somewhere in the sheet. (I don't want the total hour cover each day's work time)

Is there anyway I can do it?

View 5 Replies View Related

Find Courses That Employee Has Not Taken From A List?

Dec 10, 2013

looking to find a formula to find the courses that an employee has not taken from a list. I have a list of courses in column F, in which I can compare to column C using their EmpID number from column A and obtain a result as taken or missing in column D. I am currently using :

[Code].....

or

[Code] ....

to get the results in column D. However, with these two formulas I am getting all the courses taken by each employee and not the missing courses.

View 8 Replies View Related

Count Employee For Each Week Ending?

Jan 29, 2014

I have data that is added to every week. I need an equation to count how many times each employee show up each weekending.

View 3 Replies View Related

Excel 2003 :: Log Out Duration Per Employee

Feb 27, 2014

I have a Log In/Log Out report that I need pulling data from.

Ultimately what I want to do is get the amount of time my employees were logged out.

I am using Excel 2003. Attached is an example of the data I'm working with.

Sheet 1 contains the data, and Sheet 2 will be the summary.

So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.

If there is nothing in the next row down in column D, then do nothing.

I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.

View 1 Replies View Related

Auto Update Employee Availability?

Jul 5, 2014

I modified an employee schedule that I found on here to meet my needs but I am needing one more thing:

I need a field (or separate worksheet in the workbook) where I can enter employee availability and if they are unavailable for a specific day, it will auto update in the calendar stating they are unavailable for that day.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved