IF Question or other ?

[Ghost666th]

Column "A" = Can give a figure from 0 to 99
Column "B" = Will determin if "A" returns IF values for > than 39,31,23,15,7.
Coumn "C" = I would like it to place all figures above Column "B" Answer.

ie.

J.Bloggs "A" 56 "B" 40 "C" 16

How do i get "C" ?

New to Excel, did my course Friday (Advanced) but it only goes so far

 

[jeffreybrown]

Hi & Welcome to the forum,

It seems with your example

J.Bloggs "A" 56 "B" 40 "C" 16

it is as easy as =A1-B1 to get 16, but I'm sure that is not the case. Your description of Column B is a little confusing

Column "B" = Will determin if "A" returns IF values for > than 39,31,23,15,7

Is it possible to provide more examples with expected results?

http://www.excel-jeanie-html.de/index.php?f=1

 

[Ghost666th]

J.Bloggs "A" 31.5 "B" 24 "C" 7.5

"B" is based upon the working week of 8hr per day, so as long a a minum of 8hr per day is done it will return BASIC in incriments of 8 ie.

Monday - 8
Tuesday - 16
Wednesday - 24 etc etc

I ask that "C" gives the excess hours worked, ie

Monday - 10hr - "B" will say 8 , im asking that "C" says 2
Tuesday - 10 - "B" will say 16, im asking that "C" says 4

???

Thanks

And thank you for the welcome.

 

[jeffreybrown]

My guess is there are a million and 1 ways to do this, but how about this. There may need to be some other error checking involved. What if A is less than B or would that not be possible?

Sheet3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Mon</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Tue</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">Wed</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">32</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Thu</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">40</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Fri</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(ISNUMBER(A1),8,"")</TD></TR><TR><TD>C1</TD><TD>=A1-B1</TD></TR><TR><TD>B2</TD><TD>=IF(ISNUMBER(A2),B1+8)</TD></TR><TR><TD>C2</TD><TD>=(A2-8)+C1</TD></TR><TR><TD>B3</TD><TD>=IF(ISNUMBER(A3),B2+8,"")</TD></TR><TR><TD>C3</TD><TD>=(A3-8)+C2</TD></TR><TR><TD>B4</TD><TD>=IF(ISNUMBER(A4),B3+8,"")</TD></TR><TR><TD>C4</TD><TD>=(A4-8)+C3</TD></TR><TR><TD>B5</TD><TD>=IF(ISNUMBER(A5),B4+8,"")</TD></TR><TR><TD>C5</TD><TD>=(A5-8)+C4</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Ghost666th]

A will always be greater than B

However your table has answerd a question i made difficult with a simple formula, spent to much time today putting what i recently learnt into practice.

Wasted ur time when a little thought could have resolved.

Sorry & THANK YOU.

 

[jeffreybrown]

Glad you were able to figure it out.

Here are a couple of employee time tracking sheets that could give you some more insight.

http://www.cpearson.com/excel/Download.htm (Overtime)

http://spreadsheetpage.com/index.php/file/employee_time_sheet/

 

[lenze]

Hi and welcome to the board!!

=IF(A2>39,B2-40,IF(A2>31,B2-32,IF(A2>23,B2-24,IF(A2>15,AB-16,B2-8))))

lenze

 

[scottylad2]

Does your worksheet always stay within a single day? I have one that was given from this board that will still do a good calculation even if your workbook/timecard goes from one day into the next, like for a shift worker who starts at 18:00 pm and works till 02:00 am the next day...

Excel Workbook
	K	L	M	N	O	P
19
20		Start time	end time	hours worked	overtime
21		18:00	02:00	8:00	0:00
22		18:00	03:00	9:00	1:00
23		18:00	02:45	8:45	0:45
24		17:45	04:00	10:15	2:15
25		10:00	18:45	8:45	0:45
26
Sheet1

In this example O20 is actually showing overtime but in reality the cell contains 08:00 hours, i've just formatted it to show "overtime"

Hope this helps also

 

[scottylad2]

Excel Workbook
	M	N	O	P
27
28		BASIC	overtime
29		8:00
30		9:00	1:00
31		8:45	0:45
32		10:15	2:15
33		8:45	0:45
34		08:00
35		08:00
36		08:15	0:15
37
38
39
Sheet1

and this is another version using the same formatting if all you wish for is a total hours input box

I'm just giving additional options, i appreciate you've already found a solution

 

[scottylad2]

copy into o29 and drag down