IF, with more than 1 result option.

[fintail99]

Hi there, I already have a staff timesheet spreadsheet with a formula to determine number of hours worked (cell D1 below), but require another formula to insert a value for 'break time'; if D1 = 6 or more hours worked, I require the new formula to insert "0.5" in C1. If D1 = 9 or more hours worked, I require the new formula to insert "1.0" in C1.

A1: Enter start time
B1: Enter end time
C1: <new formula="">
D1: Calculate hours worked using formula =(B1-A1+(B1<a1))*24


Any help would be appreciated. Thank you.</a1))*24
</new>

 

[JonXL]

In C1:

=VLOOKUP(B1-A1,{0.25,0.020833333333333;0.375,0.0416666666666666},2,TRUE)

Then in D1:

=B1-A1-C1

 

[fintail99]

Thank you JonXL for the speedy solution. I should've mentioned that I require the new formula to work for overnight shifts (eg 9pm-9am).

My original formula does work for overnight and also converts the resulting figure into decimal format rather than time format. Would it be possible to convert your formula into decimal format? Thank you again.

In C1:

=VLOOKUP(B1-A1,{0.25,0.020833333333333;0.375,0.0416666666666666},2,TRUE)

Then in D1:

=B1-A1-C1

 

[JonXL]

What is your original formula?

 

[fintail99]

Original: =(B1-A1+(B1<A1))*24
This works in decimal format.

What is your original formula?

 

[JonXL]

All I see when you paste that is:

=(B1-A1+(B1

And that's not a complete formula.

 

[snjpverma]

fintail, your formula is getting cut everytime i guess. so if your formula contains < or > symbol, then keep a space around these symbols to avoid the formula from getting cut.

 

[thisoldman]

Does this help?

[TABLE="class: grid"]
<tbody>[TR]
[TD="align: right"]20:00[/TD]
[TD="align: right"]04:30[/TD]
[TD="align: right"]8:30[/TD]
[TD] ← =B1-A1+(A1>B1)[/TD]
[/TR]
[TR]
[TD="align: right"]04:00[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]8:30[/TD]
[TD]formatted as [h]:mm[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD="align: right"]20:30[/TD]
[TD="align: right"]8:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20:00[/TD]
[TD="align: right"]04:30[/TD]
[TD="align: right"]8.5[/TD]
[TD] ← =24*(B5-A5+(A5>B5))[/TD]
[/TR]
[TR]
[TD="align: right"]04:00[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]8.5[/TD]
[TD]formatted as General[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD="align: right"]20:30[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[fintail99]

Hi there, sorry about the delay and missing characters. Hope this works... my formula is =( B1 - A1 + ( B1 < A1 ) ) * 24, which works in decimal format and overnight. I can't seem to get the vlookup formula to work for less than 6 hours and overnight. Also, would it be possible to adjust it so that 6 hours & over return 0.5 hrs and then 12 hours & over return 1.0 hrs (both n decimal format). Thank you.

Does this help?

[TABLE="class: grid"]
<tbody>[TR]
[TD="align: right"]20:00[/TD]
[TD="align: right"]04:30[/TD]
[TD="align: right"]8:30[/TD]
[TD] ← =B1-A1+(A1>B1)[/TD]
[/TR]
[TR]
[TD="align: right"]04:00[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]8:30[/TD]
[TD]formatted as [h]:mm[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD="align: right"]20:30[/TD]
[TD="align: right"]8:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20:00[/TD]
[TD="align: right"]04:30[/TD]
[TD="align: right"]8.5[/TD]
[TD] ← =24*(B5-A5+(A5>B5))[/TD]
[/TR]
[TR]
[TD="align: right"]04:00[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]8.5[/TD]
[TD]formatted as General[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD="align: right"]20:30[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JonXL]

If someone clocks in at 9:30 PM and out at 9:30 AM the following day, what would be entered in cells A1 and B1 (including their formatting) and what would you expect to see in cells C1 and D1 (including their formatting)?