# IF Question or other ?

#### Ghost666th

##### New Member
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### jeffreybrown

##### Well-known Member
Hi & Welcome to the forum,

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

##### New Member
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

##### Well-known Member
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

##### New Member
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.

#### lenze

##### Legend
Hi and welcome to the board!!
Code:
``=IF(A2>39,B2-40,IF(A2>31,B2-32,IF(A2>23,B2-24,IF(A2>15,AB-16,B2-8))))``
lenze

##### Well-known Member
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
KLMNOP
19
20Start timeend timehours workedovertime
2118:0002:008:000:00
2218:0003:009:001:00
2318:0002:458:450:45
2417:4504:0010:152:15
2510:0018:458:450: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

##### Well-known Member
Excel Workbook
MNOP
27
28BASICovertime
298:00
309:001:00
318:450:45
3210:152:15
338:450:45
3408:00
3508:00
3608:150: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

##### Well-known Member
copy into o29 and drag down

Replies
1
Views
498
Replies
1
Views
887
Replies
8
Views
2K
Replies
5
Views
371
Replies
1
Views
1K

1,191,054
Messages
5,984,384
Members
439,883
Latest member
onions44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back