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>

 

[fintail99]

A1 & B1 format as Time
A1: enter 21:30, show as 9:30 pm
B1: enter 9:30, show as 9:30 am

C1 & D1 format as decimal
C1: to show 1.0
D1 to show 12.00

The above assumes 12 hours or more worked.

If 6 or more hours worked, but less than 12 hours, C1 should show 0.5.

If less than 6 hours worked, C1 should show 0.0.

 

[JonXL]

Since you aren't going back to subtract the break from the value you get in D1, no changes are really needed there. For C1, you can modify the formula to use your lookup criteria supplied above and then use the regular decimal values. I have done that here, and added a lookup pair for 0 hours so it works when there are fewer than 6 hours entered:

[PLAIN]=VLOOKUP(B1-A1+(B1 < A1),{0,0;0.25,0.5;0.5,1},2,TRUE)<a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24<a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24[ code]
[/PLAIN]

For clarity, D1 still has this formula:

=(B1-A1+(B1 < A1))*24

<a1))*24[ code]

If you want to subtract the break out from that (for example, to determine productivity, etc.), you can do so by added parentheses and directly subtracting C1:

=((B1-A1+(B1 < A1))*24)-C1

<a1))*24)-c1[ code]<a1))*24)-c1[="" code]<="" html=""></a1))*24)-c1[></a1))*24[></a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24<a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24[>

 

[fintail99]

Thank you JonXL, this is brilliant. Your suggestion to subtract the break from total hours is much preferred and works very well, so I'll use this. The Vlookup requires a little adjustment... if hours worked = 6, the break should show as 0.5. Eg: A1 = 7:00 am, A2 = 1:00 pm, D1 shows 6.0, C1 should show 0.5, but presently C1 shows 0.0. Incidentally, if A2=1:01 pm, C1 does show as 0.5. Is it possible to adjust accordingly?

Since you aren't going back to subtract the break from the value you get in D1, no changes are really needed there. For C1, you can modify the formula to use your lookup criteria supplied above and then use the regular decimal values. I have done that here, and added a lookup pair for 0 hours so it works when there are fewer than 6 hours entered:

[PLAIN]=VLOOKUP(B1-A1+(B1 < A1),{0,0;0.25,0.5;0.5,1},2,TRUE)<a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24<a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24[ code]
[/PLAIN]

For clarity, D1 still has this formula:

=(B1-A1+(B1 < A1))*24

<a1))*24[ code]

If you want to subtract the break out from that (for example, to determine productivity, etc.), you can do so by added parentheses and directly subtracting C1:

=((B1-A1+(B1 < A1))*24)-C1

<a1))*24)-c1[ code]<a1))*24)-c1[="" code]<="" html=""></a1))*24)-c1[></a1))*24[></a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24<a1),{0,0;0.25,0.020833333333333;0.5,0.0416666666666666},2,true)*24[>

 

[fintail99]

I've noticed the same for 12 hours. Eg, A1 = 7:00 am, B1 = 7:00 pm, C1 currently = 0.5 but should = 1.0. If B1 = 7:01 pm, C1 does = 1.0.

Thank you JonXL, this is brilliant. Your suggestion to subtract the break from total hours is much preferred and works very well, so I'll use this. The Vlookup requires a little adjustment... if hours worked = 6, the break should show as 0.5. Eg: A1 = 7:00 am, A2 = 1:00 pm, D1 shows 6.0, C1 should show 0.5, but presently C1 shows 0.0. Incidentally, if A2=1:01 pm, C1 does show as 0.5. Is it possible to adjust accordingly?

 

[fintail99]

Sorry, I've just noticed that the overnight hours do show correctly. Eg, A1 = 7:00 pm, B1 = 1:00 am, C1 does correctly show as 0.5. If B1 = 7:00 am, C1 does correctly show as 1.0. So, only the daytime routine would require amendment.

 

[JonXL]

I see...

I entered 7pm to 7am and evaluated the formula in C1; the state right before the VLOOKUP checked for and returned the matching value was:

VLOOKUP(0.5,{0,0;0.25,0.5;0.5,1},2,TRUE)

At this point, it had just evaluated the B1 - A1 + (B1 < A1), which evaluated to 0.5 and then returned 1.00.

I then entered 7am to 7pm and evaluated the formula in C1; the state right before the VLOOKUP checked for and returned the matching value was the same as before:

VLOOKUP(0.5,{0,0;0.25,0.5;0.5,1},2,TRUE)

Again, at this point, it had just evaluated the B1 - A1 + (B1 < A1), which evaluated to 0.5. But this time the lookup returned 0.5!

I'm as perplexed as you...

Maybe someone will have insight as to why the VLOOKUP returns different values with the same input. For your purpose of getting this spreadsheet to work, you can use a few nested IFs and, based on my testing, these don't give the same erroneous results as the bugged VLOOKUP:

=IF(B1-A1+(B1 < A1)>=0.5,1,IF(B1-A1+(B1 < A1)>=0.25,0.5,0))

Test it a bunch and let me know if it's still not working...

 

[JonXL]

This was bugging me. I did some research and it seems that Excel is not storing the same number in both instances while calculating (even though it shows the same number).

By forcing Excel to only do the lookup on the number out to the third decimal place, the VLOOKUP returns the correct value. We do that by rounding the value off before performing the lookup:

=VLOOKUP(ROUND(B1-A1+(B1 < A1),3),{0,0;0.25,0.5;0.5,1},2,TRUE)

I don't know why Excel doesn't cause the same problem when doing the IF tests, since it is essentially the same test. Maybe Excel stores the values differently when doing these two calculations (VLOOKUP vs IF)...

 

[fintail99]

Oh dear, I seem to have caused a bit of a stir!

The latest VLOOKUP appears to work, as does the preceding IF statement. Would you think one is better/safer than the other to rely upon?

Whichever the case, thank you very much for your efforts and speedy solution. Makes a huge difference to the old manual process and my HR staff will be relieved!

This was bugging me. I did some research and it seems that Excel is not storing the same number in both instances while calculating (even though it shows the same number).

By forcing Excel to only do the lookup on the number out to the third decimal place, the VLOOKUP returns the correct value. We do that by rounding the value off before performing the lookup:

=VLOOKUP(ROUND(B1-A1+(B1 < A1),3),{0,0;0.25,0.5;0.5,1},2,TRUE)

I don't know why Excel doesn't cause the same problem when doing the IF tests, since it is essentially the same test. Maybe Excel stores the values differently when doing these two calculations (VLOOKUP vs IF)...

 

[JonXL]

I prefer the VLOOKUP myself - when it's appropriate.

Functionally they should be the same (though we see now that's not always the case...); however, the VLOOKUP is more versatile. If you need to add arguments, it's much easier to do with VLOOKUP: Just figure out where it needs to go and add it in with a ; separating each pair and a , separating the parts of the pair. When set to TRUE, the lookups have to go in smallest-to-largest order, but this allows you to do the graduated effect you used here; if set to FALSE, you can set the arguments in any order and it will only pull exact matches (useful if your lookup is using something like a partnumber instead of a timescale...).

VLOOKUP also makes things really easy with tables. For example, you could have had another table where you define the criteria for breaks given the hours worked and then referenced that with the VLOOKUP.

The nested IFs are good if you're matching complex criteria (ones that involve formula, for example, or tests in multiple cells) and obviously it has all the advantages of being able to do pretty much anything - it will always work even when the VLOOKUP doesn't. But it can be harder to manage and when you have very complex nested IFs, it can be difficult for you later (or some one else entirely) to figure out what the logic is behind the formula.

They have tradeoffs. In this case, I'd say the VLOOKUP is cleaner and preferable - assuming it actually works...

 

[fintail99]

The Vlookup seems to be working, so I'll go with this. Thanks for the explanation and solution. I'm sure I'll be back for more!

I prefer the VLOOKUP myself - when it's appropriate.

Functionally they should be the same (though we see now that's not always the case...); however, the VLOOKUP is more versatile. If you need to add arguments, it's much easier to do with VLOOKUP: Just figure out where it needs to go and add it in with a ; separating each pair and a , separating the parts of the pair. When set to TRUE, the lookups have to go in smallest-to-largest order, but this allows you to do the graduated effect you used here; if set to FALSE, you can set the arguments in any order and it will only pull exact matches (useful if your lookup is using something like a partnumber instead of a timescale...).

VLOOKUP also makes things really easy with tables. For example, you could have had another table where you define the criteria for breaks given the hours worked and then referenced that with the VLOOKUP.

The nested IFs are good if you're matching complex criteria (ones that involve formula, for example, or tests in multiple cells) and obviously it has all the advantages of being able to do pretty much anything - it will always work even when the VLOOKUP doesn't. But it can be harder to manage and when you have very complex nested IFs, it can be difficult for you later (or some one else entirely) to figure out what the logic is behind the formula.

They have tradeoffs. In this case, I'd say the VLOOKUP is cleaner and preferable - assuming it actually works...