# 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
ADVERTISEMENT
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
Oct 15, 2007

I have a timesheet where user updates start and end time for various tasks.

I have placed a time capture button in the excel sheet (which is simply a macro saying =now() function)

The user clicks it before starting and after finishing the task. The start and end times are captured in adjacent cells.

If the user starts the work, and goes on a lunch break say for 20 min, comes back finishes the task and captures end time, the time difference will not consider break time which is non productive.

How can I incorporate something like 'pause' option so that before he goes for lunch he can temporarily pause the time.

View 9 Replies
View Related
May 21, 2009

This is probably a very simple problem that has me going around in circles. I am attempting to set up a time roster, where I simply want to check:

If "end-time" minus "start-time" is greater than 4:00 (hrs), then deduct 00:30 (minutes) and place that result in another cell.

If it is not greater than 4:00 (hrs) then leave unchanged. I have read thru countless examples on the Forum - but I think that such great learning is driving me mad. Although I do believe that I have the correct format [h].mm - but attempts with IF's have got me confused. This is one of those "Looking down the tunnel towards the flickering light" moments.

View 5 Replies
View Related
Feb 21, 2013

Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...

Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar

Tom 40 10 0 20 ?? ??

name

2/18-2/22

2/25 - 3/1

3/4 - 3/8

3/11-3/15

Feb

Mar

tom

40

10

0

20

??

??

View 3 Replies
View Related
Jul 30, 2013

I work different length shift some of which are over-night. I'm using the formula

=IF(((C1-B1)*24)<0,(C1-B1)*24+24,(C1-B1)*24)=IF(((C1-B1)*24)<0,(C1-B1)*24+24,(C1-B1)*24)

Where cell "B" is start time and cell "C" is finish time. This calculates hours worked whatever shift I'm on.

Is there anyway I can also deduct a hours rest break if I work more than 7 hours ?

View 6 Replies
View Related
Aug 20, 2014

I need to expand on this formula.

I require a formula that will break down daily hours worked into rate categories eg Normal Time, Time & Half, Double Time.

eg. Column E = Total time worked

Column F = Normal time

Column G = Time & Half

Column H = Double Time

What I would like to do is enter hours into Column A and a formula in Column B will split of hours to a maximum amount of 7.6hrs then the remainder of the hours be placed in Column C to a maximum of 2 hours and Column D, no maximum.

These are the formula I am currently using

column B

=MIN(E2*1,7.6)

column C

=MAX(MIN(E2-7.6,2),0)

column D

=MAX(E2-9.6,0)

I would now like to be able to split the hours over 3 rows x 3 columns

Example 1

Row A B C D

1 3 3 0 0

2 5 4.6 .4 0

3 2 0 1.6 .4

Example 2

Row A B C D

1 6 6 0 0

2 21.6 0.4 0

3 0 0 0

The total hours in Column B cannot exceed 7.6 hrs

The total hours in Column C cannot exceed 2 hrs

There is not maximum for hours in Column D

View 2 Replies
View Related
May 30, 2014

So I'm having a problem trying to make this scheduling sheet.. What I did was row a would be the employee list then b,c,d and so on are mon-sun Originally in b1 I would put like 9am-5pm and c1 8-4 and so on and in row i I put the total and added a sum function but somehow it wasn't able to calculate. So then I redid the whole thing this time in row b1 i put 9am and c1 5pm and so on and again I put the total and added the function and it still came out wrong..

View 2 Replies
View Related
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
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
Mar 15, 2012

Is there a function or a macro to calculate number of hours worked from a single cell value.

For example, cell A1 has "1600 - 1715" and need it to convert to "1.25" on cell B1

View 6 Replies
View Related
Feb 6, 2008

i have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am.

As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift.

if work, 9am to 7:30pm, and have break between 2-3pm, should have 1.5hrs shift and 9.5hrs total

if work, 7:45pm-9:45pm, and have break between 8:30-9pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs

if work 3pm to 12am and have break between 7-8pm, total hrs work is 8 and shift hrs s/b 5hrs

View 9 Replies
View Related
May 5, 2006

I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!

For example

Start time: 21:00

Finish time: 06:30

Hours worked: 9.50

Start time: 12:30

Finish time: 23:00

Hours worked: 10.50

View 9 Replies
View Related
Sep 25, 2013

I'm trying to create a time sheet to calculate how many hours worked in a week, Once it reaches 40 hours, The excess over 40 hours goes into a "overtime" cell. The "40" hours remain in the regular hours cell.

Attachment 267704

View 14 Replies
View Related
Sep 17, 2012

Any way to calculate the total hours staff work based on the mininum time of the first transaction to the maximum time of the transactions. I used a DMIN and DMAX function to get those times per employee. The issue is then the time goes over from one day to the next, such as from 11 PM to 4 AM the next day. As you can see in the data below,the fourth record shows the minimum time as 12 AM and the max as 11 PM with total time worked as 23 hours. In this example, the total hours worked should be five hours.

min time

max time

total hours

7:00 AM

16:00

9:00

[Code] .......

View 4 Replies
View Related
Aug 4, 2013

My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.

View 9 Replies
View Related
Aug 6, 2007

If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.

It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.

A2=04:00

A1=23:00

Using (A2-A1)*24 give me -19.00 hours

My SS macro has a line:

s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked

Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?

View 9 Replies
View Related
Feb 16, 2012

I charge one rate for day work and one for night shifts. My spreadsheet is set to figure the total number of hours worked and I know how to multiply by dollars to get answer #1, but is it possible to use a formula to multiply times a different rate for a night shift?

For example I use =IF(B2

View 9 Replies
View Related
Jun 15, 2014

basically I am trying to make a time calculator for work, I want it to automatically add the hours up but then also minus break times and then give me a whole paid total hours worked that week.

CALCULATOR.xlsx

View 1 Replies
View Related
Sep 14, 2007

if someone called me at 4:55pm and ended the call at 5:10pm, the whole call lasted 15 minutes. So, I want to show on excel that the call lasted 5 minutes in the 16th hour of the day (4:55pm) and 10 minutes in the 17th hour of the day (5:10pm), for a total of 15 minutes.

View 3 Replies
View Related
Feb 19, 2009

Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that?

For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell).

Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )

View 5 Replies
View Related
Oct 10, 2009

When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?

View 3 Replies
View Related
Jan 29, 2010

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

View 9 Replies
View Related
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
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
Sep 24, 2012

Timekeeper to tally total hours worked by employee. When doing a pivot for sum total hours worked for FY13 it does not calculate correctly. I understand they formatted that column/custom h:mm but when I change the 8:00 hours to a number I come up with 0.33 .

I am attaching a sample file : sample time.xlsx

I just want them to get a running total of hours worked/pay.

View 5 Replies
View Related
Jul 3, 2014

I'm trying to make a way to track if I've worked more or less than the 39 hours/week I'm paid for. At the end of each week, I have a total of how many hours and minutes that I've worked .

On column C I have what I should work.

On column D I have what I did actually work

On column E I'd like to convert automatically Columd D to minutes for calculation purposes

Column F to know if I worked more or less than what I should've subtracting C and E

Column G to have an ongoing tally to know if I need to work more or less

Column H and I could probably be the same thing. Ideally what I'd like is to have a formula pull the information from column G and put it into workdays, hours, and minutes with 1 workday being 7 hours and 48 minutes.

Since I tend to work too much, I'd like to know if I've worked 3 days too much during 1 month, I can take 3 days off the next month to get everything zeroed back to where I don't owe the company anything and vice versa.

View 6 Replies
View Related
Dec 2, 2008

I am trying to create a spreadsheet that auto calculates my emp. time.

However I do not want to use military time. I can get it to work by =a2-a1 but only if it is 8.5 and 17.5. Any ideas how I can do clock in 8:30 clock out 4:30 = 8 hours?

View 7 Replies
View Related
Feb 10, 2010

I'm trying to calculate the total hours worked for two given periods over a shift , which can span two consecutive days ie. start 15:45 and finish at 00:15 the next day. Hours worked between 6am and 6pm are paid at standard rate, whilst hours worked between 6pm and 6am attract penalty rates. Hours are cacluated in 24hr time

I have attached a copy of the timesheet that we use so you can see exactly what I'm trying to achieve, and included most of the shifts that we have.

View 14 Replies
View Related
Jan 1, 1970

I can do the timesheet formula for adding the hours worked as follows:

Start Finish Total

08:45 17:15 8.5

However, it doesn't work when I fill in a whole week work of hours in this format:

Start Finish Total

08:45 17:15 08:30

It works on a daily basis, but when total hours exceeds 24, the formula get's all mixed up - how to I format the total column to account for every 5 minutes worked, which you can't do when converting to decimal??

View 14 Replies
View Related