Samurai Posted February 22, 2006 Share Posted February 22, 2006 Someone at my workplace is having a nightmare at the moment with a certain formula.Basically she's looking to put people's hours of work onto a spreadsheet minus their break.So say for example "Jennifer" starts work at 9 and finishes at 5, she'll accrue 8 hours pay, but -1 hour for lunch. Now the formula she has works fine... and it totals it up to 7 hours correctly.However, there is "John" who only works 3 hours, say 9 'til 12 but the formula is obviously taking an hour off when he doesn't have lunch. She's looking how to make it so that anyone under x amount of hours, say for example 6, doesn't get the hour taken off as they don't take breaks.Anyone know the excel command for something like this. She asked me and I was like :P I thought someone on here might have more of an idea than me, so if you can help, please do!Thanks :P Link to comment Share on other sites More sharing options...
nsane Posted February 22, 2006 Share Posted February 22, 2006 she needs to use the spreadsheet macros, but it's been YEARS since i learned that in a middle school computer class. i think it was something like...H^-B^*D^=H9...H^ being the column for all the hours worked, minus B^ the break hours used, times D^ the number of days worked = H9 the specified block you put the macro in and where the total is to be displayed. but i'm not sure if that's how they're formatted :Palso, if all the people are listed in the same ss, which it sounds like they are. she made need to write a macro for every row, instead of being able to use the entire column...1=Hours, 2=Break, 3=DaysA1-A2*A3=A4B1-B2*B3=B4C1-C2*C3=C4and so on...edit: actually, i think you can set entire columns with macros like...^1-^2*^3=^4...and it'll automaticly do each row seperately... Link to comment Share on other sites More sharing options...
Administrator Lite Posted February 22, 2006 Administrator Share Posted February 22, 2006 It would be an idea to show us how she is currently doing it. Could use the IF command or something, but more details would be handy... Link to comment Share on other sites More sharing options...
Samurai Posted February 22, 2006 Author Share Posted February 22, 2006 Ok I don't have the .xls document at home with me (I really should have sent it to my email address) - Can do this tomorrow if I can't get it right.Basically it's like this... although please note I'm using OpenOffice.I need to find a working formula that does not function until it's "triggered" by a number greater than 6.Hard for me to understand/ I hardly use Excel. Link to comment Share on other sites More sharing options...
nsane Posted February 22, 2006 Share Posted February 22, 2006 as lite said, you should be able to wrap the formula with the if command...IF total <= 6 (no.breaks) ELSE (count.breaks)...not sure the proper syntax for spread sheets tho, but it shouldn't be too much different than that :P Link to comment Share on other sites More sharing options...
Samurai Posted February 22, 2006 Author Share Posted February 22, 2006 Ok thanks guys. Hopefully this will help her enough to sort out :P Link to comment Share on other sites More sharing options...
Samurai Posted February 23, 2006 Author Share Posted February 23, 2006 Thanks! The above formula's worked a treat. This means I get paid ;)Which means a new theme coming soon ;)Anyway, much appreicated :) Link to comment Share on other sites More sharing options...
Administrator Lite Posted February 23, 2006 Administrator Share Posted February 23, 2006 You CHARGE for help? :) Link to comment Share on other sites More sharing options...
Samurai Posted February 23, 2006 Author Share Posted February 23, 2006 HAHA :) no no it was for the girl who does our wages! But I can see where you're coming from ;)I'm getting paid on Tuesday so as soon as I can transfer some cash to my PayPal account I'll go ahead and get us a better theme than the one we're using. Link to comment Share on other sites More sharing options...
Administrator Lite Posted February 23, 2006 Administrator Share Posted February 23, 2006 :)Sounds cool... Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.