Shifts Hours..

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


ADVERTISEMENT

Working Hours - Identify And Split In Different Rewards Shifts?

Aug 5, 2014

I'm currently working with MS Excel 2010 and have a document full of people with their working hours. So their start time and their end time. Now these people should get a monetary reward dependent on the different time shifts they work in. For example, someone is scheduled for a shift starting at 6:00 AM (cell A1) until 20:00 PM (Cell B1). His actual clocking-in time is 5:48 AM (Cell C1) and clocking-out time is 19:58 PM (Cell D1). There are three shift reward types:

1) Shift Regular (E1): From 7:00 AM until 18:00 PM
2) Shift night (F1): From 18:00 PM until 0:00 AM
3) Shift midnight (G1): From 0:00 AM until 7:00 AM

How can create a formula in cell E1, F1 and G1, which will first look at the actual clocking-in time and scheduled time and will say 5:48 AM is too early as you start at 6:00 AM, so 1 hour in cell G1, and 11 hours in cell E1 and 2 hours in F1 (should automatically round up 19:58 to 20:00). However, after 4 hours work someone will get 0:30 hours lunch break, which should be deducted in E1 as well. And as mentioned earlier actual clocking time should be round up to 15 minutes, so 19:53 should be 20:00 and 6:13 should be 6:15.

Also note that the cells with time are in TIME and should become numbers (e.g. 7,5 hours) in cells E1, F1 and G1.

I also added an attachment, which shows what I would like to retrieve automated in the RED boxes.

View 1 Replies View Related

Calculating Start / End Time Between Night Shifts 24 Hours?

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

Separate Simple Hours / Holiday Hours And Night Hours

Jun 27, 2014

As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.

Nights, holidays and holiday night have different price/hour.

The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…

On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!

Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)

View 1 Replies View Related

Calculate Employees Shifts On Weekends

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

Scheduling Staff To Shifts And Locations

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

Calculate Working Shifts Per Day From Monthly Report

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

Sorting Macro :: Assign Work Shifts

Jan 10, 2007

I need a macro that will take a list of 42 names and 27 locations. The names are broken down into group of shifts, 1 shift, 2nd shift and 3rd shift. We need to perform audits on the 27 locations in groups of 2 people at a time. What I'd like is a macro that would take all the names by shift and assign 2 random people to a location. All 27 locations have to be audited each week (safety), and a report filled out and turned into our safety committee. I would like to post the audit sheet with the macro on our shared drive. Can anyone help me with this.

View 9 Replies View Related

Cells Becoming Unselected When Window Focus Shifts

Aug 4, 2007

I would like to run some code against the currently selected range of cells in the worksheet, but when I click on the VB button that runs the code, the focus shifts from the worksheet to the VB app, and the cells in the worksheet are no longer selected.

I know this is probably not a question that's come up before, but does anyone have any idea how to retain the 'selected' status of the cells in the worksheet ?

View 9 Replies View Related

When I First Click On A Worksheet, Its Tab Shifts To The Left Of All Other Tabs

Dec 9, 2008

This problem started after a lot of development of various macros in a moderate sized workbook.
I've gone back to various stages of development and am unable to find what is causing this one worksheet tab to jump to the beginning of the cue (to the left) when i first click on that tab.

Thought the most likely culprit was a worksheet change macro, but even after deleting that, the behaviour persisted.

View 9 Replies View Related

Checkbox Locations Shifts In Print Preview

Nov 22, 2007

I have checkboxes (from the control toolbox) in a spreadsheet next to text. When I am in the normal view everything is where it is supposed to be. But when I do a print preview the checkbox shifts and comes on top of the text that was at the left of the checkbox.

This form has to be presentable both on paper and electronically. So both views are important.

View 3 Replies View Related

Dividing A Time Span Into Shifts - Over Lapping Days

Feb 21, 2006

I am developing a report that tracks the efficiency of industrial sorters on
an automated assembly line. The line runs around the clock in three shifts
(1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and
collects certain performance indicators, such as units processed. I am
trying to calculate the units processed during each shift on each day. For
example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I
would want 50 units credited to shift 2 and 50 units to shift 3 (we are
assuming a steady rate of processing).

My data arrives from the machine with the following columns (each run is a
separate row):

B|D|E|G|H
Run #| Start Date| Start Time| Units Processed | End date | End Time

I have successfully used the start time to determine the starting shift with
IF statements:

=IF(--E3<0.3125,3,(IF(--E3<0.6458,1,(IF(--E3<0.9792,2,3))))) and a similar
formula to calculate the end shift. This works fine if the run only spans
two shifts within the same day.

My problem is that some of the runs, run across more than one shift and even
across days. For example, a run that ran from 10:30 pm on one day to 4:30 pm
on the next, would overlap 5 shifts and two days. I need to be able to
figure out the proportion of time spent on each shift, and use that to get
the proportion of units processed during each shift on each day.

I am currently thinking of a convoluted series of nested IF statements (and
generating lots of columns to the right of my data for each potential shift
within a run), but this seems inelegant and cumbersome. Is there a more
logical way to approach this? Even more specifically, is there a function
that can pair my start/end dates and times with a set of shift start/end
times and calculate the elapsed time for each shift within a run? Something
with MATCH or VLOOKUP maybe?

View 14 Replies View Related

Calculation Of Shift Allowance Affected By Overtime And Overnight Shifts

Apr 21, 2014

I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.

All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.

The allowance is paid for all hours worked between 18:00 and 06:00.After 10 hours, a shift becomes overtime and no shift allowance is paid.Shifts are regularly worked overnight (i.e. past midnight into the following morning)

Example scenarios include:03:00 - 15:00 would pay 3 hours of shift allowance09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)03:00 - 21:00 would pay 3 hours19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)22:00 to 10:00 would pay 8 hours
And so on.

I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:

[Code] ........

The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.

The larger formula now looks like this:

[Code] ......

…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.

I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.

Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.

Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?

View 1 Replies View Related

Excel 2003 :: Spreadsheet Shifts To The Left When ENTER Key Is Pressed?

Feb 27, 2014

When I enter a value into a cell and press "ENTER" or press the Right Arrow the whole spread shifts to the left instead of just confirming the entry of the cell and moving one cell(column) to the right.

This is a spreadsheet that I have used for over 3 years so something has changed in one of the settings.

I have Office 2003.

View 2 Replies View Related

Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours

Nov 4, 2008

In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.

Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.

View 9 Replies View Related

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 View Related

Calculate Hours Worked With Lunch / Overtime / Holiday / Vacation And Saturday Hours?

Jan 13, 2014

Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.

For accounting and payroll purposes, we need the totals to display in both hour and decimal format.

So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.

View 2 Replies View Related

Difference Between Dates And Times In Days , Hours Mins ( Working Hours )

Mar 2, 2010

The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes

=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))

The following displays it in the Hrs and Mins format

=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))

View 9 Replies View Related

Adding Hours Worked Minus Lunch Break IF Over 5 Hours?

Dec 15, 2013

I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.

View 3 Replies View Related

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only

Jan 29, 2010

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View 9 Replies View Related

How To Calculate Hours Between 2 Or 3 Days Exclude Non-Working Hours

Dec 29, 2013

I'm having difficulty to calculate hours between 2 or 3 days exclude non working hours.

Attached is the example of start date with time & end date with time.

The situation is like "when the case log in till the case assist in working hours." so i will get the hours from case log to case assist.

Testing.xlsx‎

View 8 Replies View Related

Calculating Actual Hours Worked Only In Core Hours?

Aug 11, 2010

I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.

View 3 Replies View Related

Calculate Hours Between Date And Time Within Business Hours?

Apr 4, 2013

find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?

08/03/2013 13:32:00
02/04/2013 09:32:50

View 4 Replies View Related

Looking For A Formula That Can Separate Day Shift Hours From Night Hours?

Aug 6, 2013

I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?

E10 = 19:00 or start of night time hours
B3 = start time
C3 = end time
D3 = day hours workeds =24*IF(E10

View 1 Replies View Related

Count Hours Between 2 Times Based On Hours In Another Cell

Jun 11, 2008

A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.

example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.

If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.

View 9 Replies View Related

Time Scheduling: Take Out A 30 Min Break If The Hours Worked Is Over 6 Hours

Jan 13, 2009

I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.

I have so far

A B

1 11:00 7:30

=24*(B1-A1)

Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.

View 9 Replies View Related

Format Total Hours To Days, Hours & Minutes

Aug 30, 2007

1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes ) - excel cannot average and work with this number format

2) resolution - =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm - works perfectly.

Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management?

example attached.

View 9 Replies View Related

Convert Hours To Fractions Of Hours

May 28, 2009

I am attempting to convert a spreadsheet of times (listed in the format 06:15:39.62, where 06 is the hour, 15 is the minutes, 39 is the seconds, and .62 is in truncated miliseconds) into fractions of hours (so, 6.25 [NOT 6:25!]). I've so far been doing it manually for each value, which is quite tedious (doing basic division of seconds and minutes into hours, to find the fraction) but I'd like a single formula which I can then apply to the whole spreadsheet.

View 2 Replies View Related

Displaying Sums Of More Than 24 Hours, As Hours.

Feb 1, 2010

I am trying to compute a running total of hours (from row 1) in row 2 Example................

As you can see, when the sum exceeds 24 (moving to the right across row 2) the answer resets, so to speak. Cells are formatted as time. This format *seems* incapable of recognizing quantities of hours over 24 except as days, as it were. This is obviously useful in most sorts of cases but not in this sort of case.

If I simply want the aggregate number of hours expressed as such am I doomed to failure whenever the total exceeds 24? In reply to a somewhat similar enquiry elsewhere in this forum, advice was given to format a cell as Elapsed Time. I dont see such a choice in my dropdown menu.

View 2 Replies View Related

Convert Second To Hours And Mins (over 24 Hours)

Apr 21, 2009

i need to convert second to Hours and mins and can do so using:

View 3 Replies View Related







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