Hi
Thanks for looking, I am wanting to get some help with a formular please.
I am wanting to be able to input my start and finish time into my time sheet and it automatically fill in fields for hours worked.
My problem is any hours worked outside 07:00 and 19:00 need to go in a seperate colulmn and if it is less than 11 hours a 30 minute break deducted and 12 hours a 1 hour break deducted
I have attached a sample of my timesheet
Any help appreciated
Tom
Hi
Thanks for looking, I am wanting to get some help with a formular please.
I am wanting to be able to input my start and finish time into my time sheet and it automatically fill in fields for hours worked.
My problem is any hours worked outside 07:00 and 19:00 need to go in a seperate colulmn and if it is less than 11 hours a 30 minute break deducted and 12 hours a 1 hour break deducted
I have attached a sample of my timesheet
Any help appreciated
Tom
Is this what you wanted?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn almost, the figure in cell E6 should be 1 hour (6am till 7am) also was faced with another problem when the shift goes into the following day.
Thanks for looking.
Tom
Hi it is showing 0:30, because I deducted a 30 minute break, as you had specified. Maybe I misunderstood your request:
"and if it is less than 11 hours a 30 minute break deducted"
Can you explain exactly what your requirement is regarding breaks...
Apollogies should have said meal break to come out of 'normal hours'. I have also had a play and realised if I put 24:30 instead of 00:30 it then calculates. Sorry for the confusion
I'll have another look at this - to sort out the break...
Thanks you are a star!
If the TOTAL time is less than 11 hours, 30 min break; if the TOTAL time is less than 12 hours, a 1 hour break?? Please clarify... Bak in 20 mins. BtW there was a mistake in my first sheet. This doesn't address the break time, yet. So it's not finished, but the mistake is gone!
Sorry meant over 12 hours then a 1hr break
OK. Got it. Is this nearer the desired result?
Yes, however just tried changing the times (08:00 to 20:00) and the outside normal hours come up with ########
oh and over 11 hours not 12 as stated previously, over 11 hours we get a 1 hour unpaid meal break.
Last edited by bufftom; 09-14-2014 at 08:56 AM.
Im out for a while. I'll sort it ASAP.
Sorry about the #############. Sorted.
So...... If the total hours are 11 hours or less, 30 mins are taken off the normal hours. If the total hours are more than 11 hours, 60 mins are taken off the normal hours.
I have noticed that you make no mention of minimum hours needed in order to get the 30 minutes break. Are the start time fixed and does starting before or after a given start time come into play for determining any of the hour calculations? For example, if the start is 9:00 and end is 21:00 is that the same as 7:00 start and 19:00 end? Are your shifts really 12 hours with 1 hour taken for meals and is the 30 minutes also subtracted?
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Yes thats correct, had a look at it and it works fine unless there is a figure outside the 07:00-19:00, ie 20:00-08:00. I really do appreciate the time and help you have given me. I work for the ambulance service so some shifts are not 'regular' hours.
Yes its the same, minimum to get a meal break is 6 hours however minimum shift is 8 so not worth adding into the equation, and yes 12 hour shift with 2x 30 mins or by negotiation 1 60 min break.Originally Posted by newdoverman
Thanks for looking
OK... One last bit of information is needed. If the shift is 20:00 to 08:00, am I correct in assuming that ALL of that time is classified as Non-normal hours, and should appear in column E? How are the breaks dealt with? If there are no normal hours, they cannot be subtracted from normal hours.
If the shift partially overlaps with the normal day (e.g. 22:00 to 10:00 the following day), what is the desired result for normal hours (9:00, minus 60 min for a break??) and for extra hours (3:00). If not that, then what?
Hi, yes all bar 1 hour would be non normal ours, anything after 19:00 hours and before 07:00 is outside of normal hours in the event of say a shift which is 19:00 to 07:00 meal break would come out of the non normal hours coloumn.
*%$£ this is getting a bit complicated.... Maybe it's just me. Sometimes I do over-complicate things a bit
Another final question. If an 11:30 shift overlaps with normal hours by 30 min (say from 20:00 to 07:30), a 1 hour break is due. Does half come from normal hours and half from non-normal hours?
It does get slightly more complicated but going to leave that for now (sat and sun are outside normal hours) either for the meal break
Let's try this out. For now, just ignore my "workings out" at the right. see if the main Table is OK.
Yes that is it thank you, I know it is quite simple (but been working for 24 out of the last 40 hours and brain is puddled) but how would I put that/those formulas into the rest of the month?
Rep has already been added as you are great!
Enjoying a beer now. I'll add the last bit once my glass is empty!
Cheers doing the same now lol
Just to add a slightly different problem into this, can it be inserted into a word document, namely the attached?
The spread sheet is now in a final form. I'll look at the other bit (word doc) in the morning. Time for another beer.
Short answer: No. Longer answer: You can insert the sheet as a workbook in Excel; but it would be a total bo**** to use.This would be the computing equivalent of tying a sack of potatoes round Usain Bolt's waist and expecting him to win races...
Design all the "other stuff" into your Excel sheet..
Thanks I have done just that. However somehow in the editing I have lost the formula from the 1st row tried copying and pasting the formula from original but it says something is missing.
I don't know what happened when you did that - but it looks OK now.
Brilliant thank you big time!
Just out of curiosity how difficult would it be to put say a tick box on all the dates (or anywhere else if easier) so that when it is ticked it recognises it all as 'outside normal hours' for weekends
Oh yea... you did mention that earlier. Leave it with me; I think that it'll not be that difficult.
Try to break this. Month and year are selected via a dropdown box. The weekends then shade a very pale green. On weekends, all hours count as non-normal... Anything else?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks