IF, with more than 1 result option.

fintail99

New Member
Joined
Apr 4, 2017
Messages
40
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>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
In C1:

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

Then in D1:

Code:
=B1-A1-C1
 
Last edited:
Upvote 0
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:

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

Then in D1:

Code:
=B1-A1-C1
 
Upvote 0
What is your original formula?
 
Upvote 0
All I see when you paste that is:

Code:
=(B1-A1+(B1

And that's not a complete formula.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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]
 
Upvote 0
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)?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top