Forum Home
Press F1
Thread ID: 77358	2007-03-07 02:01:00	Excelling in wages with Excel	Brain_Cell_Anon (261)	Press F1

Post ID	Timestamp	Content	User
530793	2007-03-07 02:01:00	Gentle Peeps and Peepettes! Apologies in advance for 'trying' to explain!! Read on and you'll see!! I have done a (quick) search on the forum for any thing like this, but . . . . . . I am trying to do an Excel spread sheet that can work out my weekly wages . I know my hours for weeks in advance, and I know my hourly RATES! I have nine cells . A1 [start time, formated as 'Custom' 'hh:mm'] ieg 11:00 24hr time A2 [end time, formated as 'Custom' 'hh:mm'] ieg 22:00 24hr time A3 [result of A2 - A1, formated as 'Custom' 'hh:mm'] ieg 11:00 hours A4 [hourly rate, formated as 'Currency' 'to 2 decimal points'] ieg $10 . 00 A5 [result of A3 * 24 * A4, formated as 'Currency' 'to 2 decimal points'] ieg 11 * 10 = $110 . 00 So far: Formated cells A1, 2 & 3 as 'Custom' - 'hh:mm' and A4 and 5 as 'Currency' right?!?! That works! So to find a weekly result I just do this seven times and 'sum' the result! :thumbs: BUT, some of my shifts start in one day and finish in the next . So format cells A1, 2 & 3 as 'Custom' - 'dd/mm/yyyy hh:mm' . Still fairly easy, and still seems to work! :thumbs: Now remember those other cells? A6 [overtime rate, formated in currency] ieg $15 . 00 Ok, just to throw a floppy disk in a DVD drive! My overtime is NOT 'hourly' dependent . Overtime starts at 20:00 (8pm) every day, and NOT after 8 hours of working . So if I start at 09:00 (9am) and finish at 19:30 (7 . 30pm) I get no overtime, but if I start at 15:00 (3pm) and finish the next morning at 00:57 (57mins past midnight the following day) I will get 4 hours and 57 mins overtime (20:00 to 00:57) . Oh, plus the 5 hours of normal time (15:00 to 20:00) . And now my problem(s): A7 [result of hours after 20:00 (8pm), so A2 (endtime) take 20:00 maybe????? formula = A2 - 20:00 maybe????? formated as Custom, hh:mm maybe?????] :confused: I guess I could have A10 as 20:00, formated as Custom, hh:mm, so A7 could also be A2 - A10, but what 'day' would it be in?? However, I think that may confuse the issue! :waughh: And of course lastly but not leastly: A8 [result of A7 * 24 * A6 maybe??????? formated as currency] A9 [A4 + A8 formated as currency] So my fine, heavily brained people . . . any clues for the formula(s) and format(s) of A7 and A8??? Again, I hope this makes some sort of sense, and I'm sure Excel can do this, it's me with the issues!! Many regards, The Cell :D PS Oh yes! If there is another method already 'built in' to Excel or perhaps I'm coming at this from completely the wrong direction . . . . . don't hesitate to tell me, a?	Brain_Cell_Anon (261)
530794	2007-03-07 05:08:00	My brain hurts too much at the moment to try to solve this for you, so if nobody responds in the next couple of days I'll try to take a look when my head isn't hurting. Mike.	Mike (15)
530795	2007-03-07 06:34:00	I think you will have to use the time function time(hh,mm,ss) for a start, then an IF statement to decide how much time before 20:00 and how much after	SolMiester (139)
530796	2007-03-07 06:50:00	meh, had another look, cant think at the mo.....	SolMiester (139)
530797	2007-03-07 06:59:00	Had another thought, try this microsoft template, it has O/T Weekly time record (office.microsoft.com)	SolMiester (139)
530798	2007-03-08 00:09:00	Mike I'm in no hurry! Go 'settle' that mind, a? ;) SolMiester Ditto Mike and thanks for the link . Had a look, then a 'play' but I think it all boils down to the fact that the 'formula' will have to include the fact that the 20:00 hrs deadline can not be associated with any one day! I do remember something that will only take the left hand part of some data (or right hand depending) of which I think it went something like: BRAKEDATA(RIGHT,5) to which the result from 07/03/2007 15:00 would give 15:00 (5, er, bits from the right) . But I don't know what it's called, plus I may be getting confused with a programing language! :waughh: Any way, still interested to 'hear' other thoughts! Many regards, The Cell :D	Brain_Cell_Anon (261)
530799	2007-03-08 01:15:00	Hmm! I've just come across this: =RIGHT(A2,5) So if A2 is 'Custom' - 'dd/mm/yyyy hh:mm' and the result shown is: 08/03/2007 22:00 then wouldn't =RIGHT(A2,5) display 22:00??? Maybe I should of had a play FIRST then added to this thread! :illogical	Brain_Cell_Anon (261)
530800	2007-03-08 07:05:00	Hi, if I understand you correctly then assuming you have the following values... A2 = Start date and time (eg 1/01/2007 11:07:00 a.m.) B2 = End date and time (eg 1/01/2007 10:07:00 p.m.) To calculate Normal hours, cell C2 would have... =(B2-A2)-TIME(HOUR(D2),MINUTE(D2),0) which would calc to 8:53 using the above examples To calculate Overtime hours, cell D2 would have... =IF(TIME(HOUR("22:00"),0,0)<TIME(HOUR(B2),MINUTE(B2),0),B2-(DAY(B2)+TIMEVALUE("20:00")),0) which would calc to 2:07 using the above examples To calculate Total Time, cell E2 would have... =C2+D2 which would calc to 11:00 using the above examples. Cells C2:E2 would have the format H:MM hth regards, Graham	Parry (5696)
530801	2007-03-09 02:22:00	Parry Er!?!?!? Your formula in your 'C2' is: =(B2-A2)-TIME(HOUR(D2),MINUTE(D2),0) and you say: which would calc to 8:53 using the above examples Shouldn't it be 11:00 hours? 11:07 am to 10:07 pm is 11 hours, isn't it? I'm confused here! Are you taking the idea that Excel views times and dates in decimal (or is it fractions, I can't remember, sorry)! I'm afraid I've already set up my 'play' spread sheet as A1 to A9 and I'm not sure how to change it to A2 through to E2. Can I just 'cut and paste' and Excel will do all the re-calculating behind the scenes? I guess I understand that your saying that A1 and A2 in my initial question is now A2 and B2 in yours. But are you saying that the formula in your C2 should replace my simple A2-B1? If that's the case, my C2 is correct in saying 11 hours, but your A3 is saying 8.53 hours! I'm sorry, I've lost the plot there a bit. Does your D2 actually display the hours FROM a time to the 'finish' time, as that does seem to do what I'm after! Hey, I'll copy what you've said to my play sheet as a new, separate bit, have a play and get back to you. I've just come off a 13 hour shift and it's just gone 02:10 in the AM. As I don't have to be in work until 15:00 this afternoon, I'll have a play when I get up! Many regards, The Cell :D	Brain_Cell_Anon (261)
530802	2007-03-09 03:32:00	Parry Er!?!?!? Your formula in your 'C2' is: and you say: Shouldn't it be 11:00 hours? 11:07 am to 10:07 pm is 11 hours, isn't it? I'm confused here! Are you taking the idea that Excel views times and dates in decimal (or is it fractions, I can't remember, sorry)! Parry also gave you a formula to put into D2 which calculates the overtime - don't forget to include that. If that formula is in D2, then the value in C2 should be just the normal hours, not the total hours. C2+D2 (normal hours + overtime) = E2 (total hours). Mike.	Mike (15)
1 2