ATTN: Excel Experts, Need some help.
05-20-2005, 09:32 AM
#1
Snootchie Bootchies!!
Thread Starter
Join Date: Dec 2002
Location: Hawaii
Posts: 2,013
Likes: 0
Received 0 Likes
on
0 Posts
ATTN: Excel Experts, Need some help.
hey guys I am trying to create a spreadsheet for my company's payroll but problem I am having is that I want a cell that shows overtime hours earned. So my formula is =G4-40 but if there is 0 hours then it shows -40, how do you make it so it just shows 0 or an empty cell.
So if joe has 43 hours and john has 38,
thanks guys I hope that was clear enough to understand.
So if joe has 43 hours and john has 38,
Code:
Name Hours Overtime Hours Joe 43 3 John 38 0 (but it shows as -40 not 0)
thanks guys I hope that was clear enough to understand.
05-20-2005, 09:37 AM
#2
Join Date: Oct 2000
Location: Location Location
Posts: 44,908
Likes: 0
Received 0 Likes
on
0 Posts
put in an if condition. i forget the exact formula but you can say like if value G4 is >0
__________________
'00 Dakar Bus CRS Edition
LCD Squad #0001
'00 Dakar Bus CRS Edition
LCD Squad #0001
Originally Posted by WiLL
...I really wanna get out and shoot people.
05-20-2005, 09:44 AM
#3
#CustomUserTitle
Join Date: Mar 2002
Location: glass case of emotion
Posts: 63,854
Likes: 0
Received 0 Likes
on
0 Posts
05-20-2005, 09:54 AM
#4
Snootchie Bootchies!!
Thread Starter
Join Date: Dec 2002
Location: Hawaii
Posts: 2,013
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by e3NiNe
dude you are awesome but I dont get the formula though. it works though. can you explain it to me?
05-20-2005, 09:58 AM
#5
#CustomUserTitle
Join Date: Mar 2002
Location: glass case of emotion
Posts: 63,854
Likes: 0
Received 0 Likes
on
0 Posts
=IF(B4>=40.01,B4-$B$1,"0")
so, if the hours worked (cells B4 - B whatever) are greater than 40.01, then you take the hours worked (cells B4 - B whatever) - what you consider full time (40 hours in cell B1).
By putting the $ dollar signs, it'll lock that cell in place when you click and drag the forumla down to other cells.
if hours worked is not > 40.01, then print a zero
so, if the hours worked (cells B4 - B whatever) are greater than 40.01, then you take the hours worked (cells B4 - B whatever) - what you consider full time (40 hours in cell B1).
By putting the $ dollar signs, it'll lock that cell in place when you click and drag the forumla down to other cells.
if hours worked is not > 40.01, then print a zero
05-20-2005, 09:59 AM
#6
snitches get stitches
Originally Posted by NeverBX
dude you are awesome but I dont get the formula though. it works though. can you explain it to me?
This is saying:
If the value in B6 is greater than 40.01, then the resulting value in the new cell will be the value of B6 minus the value of B1. If not, it will = 0.
05-20-2005, 10:31 AM
#7
Snootchie Bootchies!!
Thread Starter
Join Date: Dec 2002
Location: Hawaii
Posts: 2,013
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by e3NiNe
=IF(B4>=40.01,B4-$B$1,"0"
so, if the hours worked (cells B4 - B whatever) are greater than 40.01, then you take the hours worked (cells B4 - B whatever) - what you consider full time (40 hours in cell B1).
By putting the $ dollar signs, it'll lock that cell in place when you click and drag the forumla down to other cells.
if hours worked is not > 40.01, then print a zero
so, if the hours worked (cells B4 - B whatever) are greater than 40.01, then you take the hours worked (cells B4 - B whatever) - what you consider full time (40 hours in cell B1).
By putting the $ dollar signs, it'll lock that cell in place when you click and drag the forumla down to other cells.
if hours worked is not > 40.01, then print a zero
Originally Posted by RB
=IF(B6>=40.01,B6-$B$1,"0"
This is saying:
If the value in B6 is greater than 40.01, then the resulting value in the new cell will be the value of B6 minus the value of B1. If not, it will = 0.
This is saying:
If the value in B6 is greater than 40.01, then the resulting value in the new cell will be the value of B6 minus the value of B1. If not, it will = 0.
05-20-2005, 10:47 AM
#9
Join Date: Oct 2000
Location: Location Location
Posts: 44,908
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by flipped cracka
greater than or equal to, not just greater than.
like i said i don't remember the exact formula
h:
__________________
'00 Dakar Bus CRS Edition
LCD Squad #0001
'00 Dakar Bus CRS Edition
LCD Squad #0001
Originally Posted by WiLL
...I really wanna get out and shoot people.
05-20-2005, 12:20 PM
#10
BOOM goes the dynamite!
Originally Posted by DaKarMaul
like i said i don't remember the exact formula h:
h: