need help reducing value by 0.5 if sum is >8

[rmckeon]

I'm subtracting one cell from another to get a SUM. I'd like reduce the SUM by 0.5 if the value is greater than "8". In other words, if A1-A2 is 9, I would like the number displayed to be 8.5. can this be done?

 

[Scott Huish]

=(a1-a2)-("00:30"*(hour(a1-a2)>8))

 

[rmckeon]

=(a1-a2)-("00:30"*(hour(a1-a2)>8))

Tried that and get #NUM! error

 

[rmckeon]

Tried that and get #NUM! error

I tried the following, which results in a sum value but it still doesn't pick up the reduction of 0.5 (or 00:30): =((C16-C15)-((C16-C15)>8)-"00:30")*24

 

[rmckeon]

I tried the following, which results in a sum value but it still doesn't pick up the reduction of 0.5 (or 00:30): =((C16-C15)-((C16-C15)>8)-"00:30")*24

I guess this one is a bit perculiar?

 

[Scott Huish]

What exactly is in A1 and A2?

 

[rmckeon]

What exactly is in A1 and A2?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,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"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD style="BORDER-RIGHT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-BOTTOM: #f0f0f0 3px outset">A</TD><TD style="BORDER-RIGHT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-BOTTOM: #f0f0f0 3px outset">B</TD><TD style="BORDER-RIGHT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-BOTTOM: #f0f0f0 3px outset">C</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-RIGHT: #f0f0f0 3px outset; BORDER-TOP: #f0f0f0 3px outset; BORDER-LEFT: #f0f0f0 3px outset; BORDER-BOTTOM: #f0f0f0 3px outset; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">16:30</TD><TD style="TEXT-ALIGN: right">07:30</TD><TD style="TEXT-ALIGN: right">9</TD></TR></TBODY></TABLE>
I'm trying to say "if C1 is above 7.999, take away 0.5". In other words, 8.5 would turn into 8.0, 10 would turn into 9.5, etc. BUT, anything below 8 would stay the same - for example, 6 would stay 6, 3 would stay 3, etc.

I hope I'm explaining this okay.

Thanks!!

 

[Scott Huish]

OK, first that is A1 and B1, unlike your original question, but that aside you have a whole number in C1. Are you multiplying the result by 24 to get a decimal value?
You said you were using A1-A2, but that should result in a time value, not a whole number, please clarify.

 

[rmckeon]

OK, first that is A1 and B1, unlike your original question, but that aside you have a whole number in C1. Are you multiplying the result by 24 to get a decimal value?
You said you were using A1-A2, but that should result in a time value, not a whole number, please clarify.

If the difference between the two times (be they A1 and A2, or A1 and B1) is greater than 7.99 I want to take away 0.5.

I'm using a formula to subtract one from the other to give me a net time. I'm stuck on how to get it to take the number down 0.5 hours (or 30 minutes) if it reaches 8.00 hours or above.

Thanks for your help.

 

[Scott Huish]

What I am trying to say is that you can't get a result of 7.99 unless you convert the time to decimal first.

A straight subtraction will result in 9:00, which is not equal to 9 decimal. It is equal to 9/24 (or 3/8), because Excel stores times as a fraction of a day.

=(A1-B1)-("00:30"*(A1-B1>(1/3)))

 

[rmckeon]

What I am trying to say is that you can't get a result of 7.99 unless you convert the time to decimal first.

A straight subtraction will result in 9:00, which is not equal to 9 decimal. It is equal to 9/24 (or 3/8), because Excel stores times as a fraction of a day.

=(A1-B1)-("00:30"*(A1-B1>(1/3)))

Looks like we got it. THANK YOU !!!!!