Lorry Driver Shifts (Day / Night / Saturday / Sunday) Variable Payrate
Dec 15, 2013
I have attached the xlx sheet. I am a truck driver and I always have to calculate my weekly wage on paper.
I start everyday at different times it varies the end of shift as well. We have day shift, night, saturday, sunday payrates.
I have done an excel sheet I was trying for a couple of days reading forums and watching videos but just I couldn't.
Example if I start Friday at 18:00 and finisth Saturday at 04:00. I have in total 10 hours.
Day shift: 1 hour
Night shift: 5 hour
Saturday: 4 hour
So in one shift I get 3 different payrate. In one shift if I work more than 6 hours it must be deducted 00:45 minutes.
Day shift: 07:00-19:00 - £10.7
Night shift: 19:00-07:00 - £11.7
Saturday - £12.7
Sunday - £14.7
Attached File : DRIVER SHIFTS VARIABLE SHIFTS AND PAYRATES.xlsx‎
View 7 Replies
ADVERTISEMENT
Dec 26, 2013
We have a person who work in 24/7 support. One person work passive 24 hour, but it 24 hours are divided in to next shifts: normal 06-18, over hours 18-22, night hours 22-06.So if we have some one who is supporting client say from 17h till 23h, we have next results:
Name Start time End Time 06:00 - 18:00 18:00 - 22:00 22:00 - 06:00
John 17:00 23:00 1:00 4:00 1:00
Is it possible to in time range enter formula who subtracted end time from start time, but taking into account 24 hours?
View 9 Replies
View Related
Aug 17, 2012
I have an excel sheet that uses date formulas in order to provide data regarding the date that it brings up. My problem is that the formulas do not bring up dates unless the dates are on a weekday. Ex. I would like to look back 3 months in the past, and I do so using the formula =today()-90 (90 for 90 days, about 3 months). I would then like to input this date formula into another function (potentially an if function) so that if today-90 is a saturday or a sunday, the function will automatically bring up the date for the friday preceding it.
View 2 Replies
View Related
Oct 17, 2007
I am trying to format a row (B) if B8 = Saturday or Sunday.
The value in B8 is derived from a formula.
I have tried the following but it only works if cell is typed manually.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("B8")
If .Value = "Saturday" Or .Value = "Sunday" Then
Me.Range("B8:J8").Font.ColorIndex = 2
End If
End With
stoppit:
Application.EnableEvents = True
End
End Sub
I also want to change format back if cell changes to M-F.
Excel 97
View 9 Replies
View Related
Oct 5, 2013
I have three columns named "name","start_date" and "end_date"
ex:
Name start_date end_date
AB 9/11/13 10:19 AM ??
CD 9/12/13 11:45 AM ??
All i need to find is,
If Name = AB, i want put start_date+3 days in the end_date column,
similarly for name = CD , start_date+7 days.
But the calculation should exculde weekends(sat and sun).
ex: For AB, start_date = 10/3/13 (3rd oct , thrusday), end_date should be start_date+3 ,so end_date = 10/6/13 but 6th october is sunday so the formula should give end_date as 10/7/13.
View 4 Replies
View Related
Aug 4, 2014
I have below formula which gives me the due status.
[Code] ......
How can I modify the code so it excludes Saturday and Sunday.
View 7 Replies
View Related
Oct 25, 2013
I'm wondering if there is a way to highlight Saturday and Sunday on my graph: [URL] ......
Here, I have it done with a shape formatted with the transparent light red fill.
View 2 Replies
View Related
Aug 4, 2013
I am trying to automatically get the following series filled in but have failed thus far:
1,2,3,4,5,6,7,11,12,13,14,15,16,17,21,22,23....... (after 7, it skips 3 digits)
Similarly,
Excel has the "Fill Weekdays" option. However, how do I also remove Friday along with Saturday and Sunday from the dates of a year?
View 4 Replies
View Related
May 12, 2013
=IF($C2="","",IF(NETWORKDAYS($A2,$C2+1)
View 9 Replies
View Related
Feb 8, 2012
Can weeknum work with the week starting on saturday instead of sunday or monday?
if not, what are my options?
View 4 Replies
View Related
Jan 16, 2014
I am looking for Macro code preferably to get list of dates with Saturday / Sunday in a separate columns which falls Saturday and Sunday on imputing the year.
View 5 Replies
View Related
Aug 24, 2014
I have a list of dates and I need a formula that will return the date of the following Monday, IF the date falls on a Friday, Saturday or Sunday.
View 3 Replies
View Related
Mar 11, 2014
I am trying to write a macro to send mail on every Friday and also on last day of every month. If the last day of the month falls on Saturday or Sunday then the macro should mail on Friday. I have written a separate macro to send a mail. I have also written to check day(ie Monday, Tuesday etc) of today. If today is Friday or month end i can send mail. I dont know how to tell the macro to send mail on friday if the month last date is saturday or sunday.
Sub done()
Dim Dat As Date, x As Integer, y As Date, sorry As String
Dim str As String
sorry = "Today is not friday or month end. So i cannot send mails"
str = WeekdayName(Weekday(Now()))
[Code] ........
View 1 Replies
View Related
Nov 17, 2012
I am trying to update a spreadsheet for 2012 to 2013 and want to use a formula to change SUNDAY 8 January to Sunday 9 January and so on for rest of year.
View 1 Replies
View Related
Feb 16, 2012
Last week I was having no problem accessing the company's Database using Excel 2010. Thursday, my computer was replaced during an upgrade to Microsoft 7. Now, I am getting this Microsoft [ODBC Driver Mismatch] error. The exact message is:
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.
View 4 Replies
View Related
Feb 3, 2009
I finally completed a macro that would loop through selected workbooks in a folder and pull results to be shown in a new workbook.
This works fine by using a worksheet where the files are listed and in a1 of the worksheet, the path to the folder.
We have one workstation on the LAN with restricted access to the server, and therefore has mapped drives. Therefore the path to the folder containing the workbooks is different.
How could I instruct the macro when invoked - so in the case of that workstation - if the macro cannot find the files at the path stated in a1 of the workbook, it is to try a different location (The folder containing the workbooks is actually Drive:T)
View 10 Replies
View Related
Jul 30, 2006
1: two colums of data for each car (lap distance covered and speed). Now each driver is going to decide to brake at different points.
2: i have coner data (eg corner 1 is at 150meters to 200 meters, corner 2 is at etc)
3. I am trying to create something that will do the following.
determine the lap distance at which the driver is braking (the point where speed decreases). I would like to do this for each corner and each driver. I am struggling to find a way of determining how to read the "lap distance" value when the corresponding "speed" value stops increasing and actually starts to decrease. This is the point at which the driver is braking
View 9 Replies
View Related
Oct 1, 2006
I wish to add a add in in exel XP 2002 with Windows SP2. For that I have three file has to install:
1 fichier.lgc has to install in Windows applog
First problem I do not find this repertory hidden under same XP SP2 with the good options to select in Windows to explore. Thus I have to arrange this file in Windows appPatch and in Windows Prefetch in the place and the problem comes from I think.
1 fichier.xll has to install in office xlstart
1 fichier.ppp has instaler in office xlstart
And when the double click on the fichier.xll for the throw the error message is as follows “WinRT Driver not installed”. Thus my questions are is this normal that I do not find the file Windows applog under win XP SP2 and how to install WinRT driver. Or then where to arrange my fichier.xll which must normally be in the repertory Windows applog
View 4 Replies
View Related
Feb 20, 2010
In the attached WB - the formula, in Col. "D", seems to be too long - although returning the expected result. Could somebody lighten my eyes with a much shorter formula ?
View 3 Replies
View Related
Feb 8, 2010
Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.
Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.
To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?
View 9 Replies
View Related
Jan 26, 2009
I have a timesheet that the manager fill in every night for the workers, simply it has a start time and end time and it calculates the hours worked (all times are entered in quarters of an hour ie 9.25 for 9:15 etc). At the moment i can work out if a person is on day or night with:
View 2 Replies
View Related
Jan 31, 2008
I have a spreadsheet that calculates the total working hours of our warehouse staff, overtime, etc... using some good advice that has been published here before.
Cell C1 : starting hour
Cell D1 : ending hour
The hours are filled in, using 1904 system, without date as 08:00, 12:00, 21:00, etc... total hours is calculated as follows:
=(D1-C1+(D1
View 9 Replies
View Related
Jan 27, 2013
get a formula to calculate hourly rates from a matrix for different areas and daytime and night.
View 4 Replies
View Related
May 3, 2013
In a sheet, like the example, I have monthly shift schedules for employees. What I want is to count the total shifts for Saturday and Sunday, separately Night (N) and Morning (M)/Afternoon (A) shifts, as in columns with red.
ABCDEMNOPQRAGAHAIAJ1JUNE 20132DATE1234121314151617....
HOW MANY "N" HOW MANY "M" AND "A"3SATSUNMONTUEWEDTHUFRISATSUNMON....
AT SAT-SUNAT SAT-SUN4Employee1NNNNNMM--A205Employee2MMMM-NNNNN246
Employee3AAAAM--MMM067Employee4--MM-AANNM408............................................9EXAMPLE
View 6 Replies
View Related
Jun 10, 2014
I have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
Any way that I can do this in Excel?
View 1 Replies
View Related
Aug 17, 2008
What I was told to do was to create an Excel Macro that would record 7 fields via pop-up box. I had no problem with this (see code below) but now I would like to run that same macro for 10 or more students. I would like to have the same data collected (first name, last, email, address, phone, etc) for each student, but have excel compile a list of the recorded data for each individual. Ideally John, Mary, Will, Greg, and Matt would fill out the form and their responses would appear as:
First Last Email Phone Other
John Doe johndoe@yahoo.com 5551245 etc etc
Mary Jones mjones@gmail.com 5551227 etc etc
Next Student student@google.com 5551212 etc etc
The macro is started by clicking on a button to register for a free giveaway
Originally Posted by code
Sub RetisterToWin()
Dim FirstName As String
Dim LastName As String
Dim Email As String
Dim MailingAddress As String
Dim PhoneNumber As String
Dim Major As String.............
View 9 Replies
View Related
Jan 15, 2009
I'm trying to calculate the hours worked for both my day shift and my night shift.
Day shift (thanks to search ) I have managed to figure out and worked quite well.
=ROUND((E7-D7)*96,0)/4
It totals adds up the time and converts it into a decimal of hours worked.
For example Joes starts at 1100 and finishes at 1330 it returns a total of 2.5 hours worked.
However I strike a problem with nightshift.
They start in the late afternnon and work thoguh into the am.
I have used the same formula but it doesn't seem to work:
=ROUND((K7-L7)*96,0)/4
I assume because once the clock strikes 12 it's a new day and it can't work out the maths.
Lets use the example form about but make it pm.
Joe starts at 2300 and finishes at 0130 it should give me a total of 2.5 hours instead it gives me 21.5 hours
View 9 Replies
View Related
Sep 29, 2012
I would like to write a macro that can return the number of hours worked during the night shift.
In this case, the night shift starts at 21:00pm and ends at 6:am next day.
The column "I2" returns the number of hours worked during the day - formula ((F3-C3+(F3<C3))-(E3-D3+(E3<C3)))*24);
The column "J2" gives the overtime hours taking into account the number of regular hours allowed - IF(OR(I3="",I3<6),"",IF(I3>H3,"",I3-H3));
Cells "C3:F13" allows users to set up (using a data validation list) starting time, lunch in, lunch out, end time ( columns C and D AM; E and F PM);
My question is: How can I calculate night hours in column K, without having conflits with numbers returned in column I (worked hours)? Is it possible to write a macro for this?
Below please see the table:
B
C
D
E
F
G
H
I
J
K
1
Name
Start Time
Lunch In
Lunch Out
End time
BREAK?
Regular hours
Worked Hours
Overtime
Night Shift
[Code] ......
View 1 Replies
View Related
Dec 31, 2009
I have table and need to take out montly total for each worker...
Now...
Each hours in day have own factor. (I need total hours per day but for illustration)...
So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours
When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours
When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours
Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday).
Here is some attachment:
Book1.xls
I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188.
Below I calculated manually those numbers wich I want to be automated...
Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...
View 14 Replies
View Related
Mar 12, 2013
(Excel 2007). I want to separate night hours 22:00-6:00 fom day hours 6:00-22:00 in my work plan.
I am using all kind of shifts for example
8:00-23:00 = 14 day hours 1 night hour
21:00-7:00= 2 day hours 8 night hours.
16:00-2:00=6 day hours 2 night hours
View 6 Replies
View Related
