Jump to content

Microsoft Excel Problem


Samurai

Recommended Posts

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


  • Replies 9
  • Views 1.6k
  • Created
  • Last Reply

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 :P

also, 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=Days

A1-A2*A3=A4
B1-B2*B3=B4
C1-C2*C3=C4
and 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


  • Administrator

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


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.

excel5jb.png

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


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


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


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


Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...