Shift Rota/calendar 5 Crews Rotating Shift 

Shift Rota/calendar 5 Crews Rotating Shift  Excel 
View Answers 
Hi All,
I am a complete novice when it comes to programming so am in desperate need of help.
I work in a lab testing fuel on 12 hour shifts covering 365 days a year.
Currently one of my colleagues produces the shift rota by manually typing in both the calendar and the shift pattern every year. Very laborious task.
Is there a way to use either formulas, macros or vba to automatically generate this by typing a year, say '2013' into a box then the whole shift populating?
The pattern is: 2 days (the very first day of the sequence is always a tuesday), 2 nights, 4 off. This repeats 7 times when we get 18 days off (so the last block doesn't have the 4 days off). This pattern applies to 5 crews / teams from shift A to E.
I have tried looking for templates etc but after weeks of looking I can't find this pattern anywhere.
Any help would be so greatly appreciated.
Thanks in advance.
 Ask any question about Excel and have it answered in no time.
Similar Excel Tutorials
I'll show you the top keyboard shortcuts for Excel that are sure to increase your productivity. These shortcuts are ...
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
How to sum a range of cells that contains errors. This requires an array formula, so it will take a few steps, but ...
How to count the number of errors in a range in Excel; also, how to count the occurrence of a specific error. Sect ...
Subscribe for Weekly Tutorials
Helpful tutorials delivered to your email!
Helpful Excel Macros
 Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
 Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
 Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
 This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Similar Topics
I have attached a schedule for shift allowance calculation. The conditions for payment are as follows:
3 & 4 shifts = 15% Basic
2 shifts = 10% Basic
If on leave for 21 working days or more, no shift allowance
If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed)
If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed)
The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.
3 & 4 shifts = 15% Basic
2 shifts = 10% Basic
If on leave for 21 working days or more, no shift allowance
If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed)
If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed)
The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.
Good afternoon!
I'm working with Excel 2003 sp 3 on Windows xp.
I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:
=INT(Q3/8)+(((Q3/8)(INT(Q3/8)))*0.8)
That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:
weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours
Total = 41 man days and 1 hour
I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".
Any advice is greatly appreciated!
Happy New Year!
~ Brenda ~
I'm working with Excel 2003 sp 3 on Windows xp.
I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:
=INT(Q3/8)+(((Q3/8)(INT(Q3/8)))*0.8)
That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:
weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours
Total = 41 man days and 1 hour
I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".
Any advice is greatly appreciated!
Happy New Year!
~ Brenda ~
Very new in this board. If I post this question in the wrong forum please bear with me.
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year JanDec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from FebDec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year JanDec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from FebDec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions
Does anyone know of a noncommercial 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 doublingup or missing 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 doublingup or missing shifts.
Hi all! Can anyone tell me the shortcut for Delete, Shift cells up? I have checked through Google but could not find the shortcut.
Thanks!
I've tried pressing shift+enter but I can't get it!
I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
Is there a way to calculate the average days between a column of dates?
Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?
Thank you in advance!
~cp
Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?
Thank you in advance!
~cp
I have a spreadsheet counting down the days until a date is reached. However, after that date is reached and passed I get a #NUM! error.
What can I do to countdown the days as below AND countdown the days that have passed as a NEGATIVE NUMBER???
=DATEDIF(NOW(),D11,"D")
What can I do to countdown the days as below AND countdown the days that have passed as a NEGATIVE NUMBER???
=DATEDIF(NOW(),D11,"D")
I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.
Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:
using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.
The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.
Anyone have any idea?
Cheers
Jon
Edit: I have read this: / /> Doesn't seem to cover the issue I describe
Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:
using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.
The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.
Anyone have any idea?
Cheers
Jon
Edit: I have read this: / /> Doesn't seem to cover the issue I describe
Hello,
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
I was able to convert start date and and end date to the number of days
inbetween the two dates. Does anyone know how to then convert the number of
days into months?
inbetween the two dates. Does anyone know how to then convert the number of
days into months?
Hello,
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKEDOR choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKEDOR choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
Let's say, if two fields in one column has been filled with numerical
increament, i.e. A1 is 2 and A2 is 4. Now I want to fill the whole column
with this increament pattern till row A20. One thing we will all do is to
left click on the fill handle and drag it down to row A20. But if the task is
to fill to row 500, the mouse draging sometime won't be easy to locate the
row. I am wondering anyone can help me to find the keyboard shortcut
equivalent to that drag fill handle action.
Thanks
increament, i.e. A1 is 2 and A2 is 4. Now I want to fill the whole column
with this increament pattern till row A20. One thing we will all do is to
left click on the fill handle and drag it down to row A20. But if the task is
to fill to row 500, the mouse draging sometime won't be easy to locate the
row. I am wondering anyone can help me to find the keyboard shortcut
equivalent to that drag fill handle action.
Thanks
I'm trying to format a worksheet to show how many days overdue certain tasks are, and running into a couple of issues. The sheet is set up with Due Dates in Column A, Days Overdue in Column B, and Date Completed in Column C. My first thought was just to use as simple a formula as possible in Column B =Today()A2. This worked alright for tasks that already had Due Dates assigned, but if Column A was blank I'd get a high number in Column B. I also realized that tasks will continue to show as overdue even after I enter the completedon date in Column C. Is there a relatively simple way to tackle this formula? Thanks in advance for suggestions. (I've attached an example with the column headings I want to use.)
I use Excel 2000
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4
1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?

Knowldege is Power
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4
1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?

Knowldege is Power
HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)
Hello, please help me out with this one.
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
I need to create a bar chart for the year. I've already have 3 bars for each of the months, a plan, an actual, and a last year. I need to be able to split out just the actual bar, so that it is a stacked bar. The other two bars need to stay and remain unstacked. Is this possible?
A previous poster explained what I also need; there were no responses,
so I thought I would try again.
Does anyone know of an Excel templateor a small standalone
programthat will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or lowcost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.
Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."
Thanks very much.
so I thought I would try again.
Does anyone know of an Excel templateor a small standalone
programthat will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or lowcost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.
Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."
Thanks very much.
Hi there. I know it sound like a really simple thing but its really stumped me.
Instead of the y axis being on the left hand side, i wish for it to cut at 0,0. I have positive and negative values and I need it to be in the centre instead of on the left.
I have tried formatting both the axis and it would appear that the x axis already cuts the y axis in the right posistion so i need to format the x axis to make the y axis cut it at 0,0. I have already tried typing in 0 instead of 1 and it keeps saying it need to be number more than or equal to 1.
Any help would be greatly appreciated.
Nicole
Instead of the y axis being on the left hand side, i wish for it to cut at 0,0. I have positive and negative values and I need it to be in the centre instead of on the left.
I have tried formatting both the axis and it would appear that the x axis already cuts the y axis in the right posistion so i need to format the x axis to make the y axis cut it at 0,0. I have already tried typing in 0 instead of 1 and it keeps saying it need to be number more than or equal to 1.
Any help would be greatly appreciated.
Nicole
Hi all,
I have a dillemma here,
I have to create a schedule which incorporates lunches automatically (either half hour or 1hour lunches, depending on the circumstances).
The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:
If you worked less than 6 hours = no lunch
If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch
If you worked more than 8 hours = 1 hour lunch
I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.
Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or halfday.
If someone can help it would be greatlyyyy appreciated
I have a dillemma here,
I have to create a schedule which incorporates lunches automatically (either half hour or 1hour lunches, depending on the circumstances).
The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:
If you worked less than 6 hours = no lunch
If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch
If you worked more than 8 hours = 1 hour lunch
I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.
Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or halfday.
If someone can help it would be greatlyyyy appreciated
Hello, I am pretty new to excel programming/messing around so I'll refer to those who are much wiser than I am. I am trying to set up a form for work, in this form I want to have a page with a bunch of cells presized. What I want to do is when I insert a picture from a job site, that when this picture gets inserted, it will automatically resize to fit within the cell. I don't have time to manually resize dozens of pictures and if I could get this to work it would be awesome! I tried to search for an answer using the search function, but I kept getting a fatal error about allowable memory? Thanks in advance for you help!
I have a spreadsheet set up with an employees information, including the start date. After each year that the employee is working, he gets bumped up on the pay scale. For this reason, I only need to know how many years the employee has been working, rounded down to the nearest year.
This is what I have so far (hire date is in column B):
=((TODAY()B4)/365)&" YEARS"
This function gives me a number with many decimal places.
I tried:
=ROUNDDOWN((TODAY()B4)/365),0)&" YEARS"
It tells me I have too few arguments. Please help!
This is what I have so far (hire date is in column B):
=((TODAY()B4)/365)&" YEARS"
This function gives me a number with many decimal places.
I tried:
=ROUNDDOWN((TODAY()B4)/365),0)&" YEARS"
It tells me I have too few arguments. Please help!
We have a large (4,000+ rows) excel worksheet from a prior employee that I
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.
Thanks