Solved: #value!

[willow0ne]

I have created a work schedule that calculates the amount of hours each person scheduled works (including 1/2 hour breaks on shifts over 7 hours) on that day. If someone is not schedule and I putt "off" in the cell I get the error #value! . How can I fix my formula to get rid of that error? The formula I am using is:
=MOD(C18-b18,1)*24-(MODC18-B18,1)>1/4)/2

Coumn B=time in
Column C= time out
Dolumn D- total shift hours

 

[bomb #21]

=IF(B20="OFF",0,IF(C20-B20<=1/24*7,C20-B20,C20-B20-(1/48)))

(unless you have any shifts spanning midnight)

 

[Zack Barresse]

Hi there, welcome to the board!

I generally try the approach from the other side...

=IF(ISNUMBER(B18),FormulaIfTrue,IfFalse)

That way any text entered will trigger the False syntax. The #VALUE! error is because you're trying to do mathematics on textual value(s).

Hiya Bomb!

 

[bomb #21]

Hi there, welcome to the board!

I generally try the approach from the other side...

=IF(ISNUMBER(B18),FormulaIfTrue,IfFalse)

That way any text entered will trigger the False syntax. The #VALUE! error is because you're trying to do mathematics on textual value(s).

Hiya Bomb!

Yeah, well ... you even drive on the wrong side.

Good to "see" you.

 

[willow0ne]

I do have shifts that go past midnigt. Do you know of a alternative formula?

 

[Zack Barresse]

What kind of values are you using? Are they time values or decmial values? If they're date/time values, just take one minus the other. Seems fairly straight forward to me...

@bomb: yup, wrong side of the road, must be the color of the lines, or the weak beer we drink.

 

[bomb #21]

I do have shifts that go past midnigt. Do you know of a alternative formula?

=(C21-B21)+(C21<B21) seems to ring a bell.

 

[Zack Barresse]

But why double the formula demand when you just have to use the right input value/format?

 

[bomb #21]

Who, me?

Basic end minus start doesn't work for time if start > end. Unless the answer is in "right input value/format", which ... I don't follow.

 

[Zack Barresse]

Start should always be greater than the end. If you enter date/time value, doesn't matter what day it falls on, as time values still have a date attached to them (serial numbers), even if it does show just a time (format). I guess my kick is if you are working with dates/times, make sure you are entering and working with dates/times.

 

[bomb #21]

I understand the explanation, thanks. Although with a date/time "regime", surely start would always be < end since ... no-one can work backwards. (?)

With hours tracking, I'd imagine that most would tend to skip the date aspect, on account of the extra input that would require. Just IMO.

 

[Zack Barresse]

Some do, sure. Even if you do enter a time, a date is always entered anyway, just usually not shown in lieu of auto formatting. It's just my opinion that it's best practices to use date/time together, especially when dealing with date/time. Realistically it doesn't take that much more time to ensure a date is entered with the time.

 

[bomb #21]

Some do, sure. Even if you do enter a time, a date is always entered anyway, just usually not shown in lieu of auto formatting. It's just my opinion that it's best practices to use date/time together, especially when dealing with date/time. Realistically it doesn't take that much more time to ensure a date is entered with the time.

Sure, but if you do enter just the time, then the "default" date is 0 so you're back to the end < start issue.

Ultimately our opinions differ, so ... that's enough "wrangling" with MVPs for today.

 

[Zack Barresse]

LOL! Absolutely, it's just the way I do things. And even if I am always right, that doesn't mean that my way is the right way for everyone... ROFL!! Okay, couldn't resist.

 

[slurpee55]

Just my two cents, but for a workschedule/timesheet a date could be a hindrance, if you mean to use it week after week. So, one must find ways around it....Zack's using the rational side of his brain too hard...maybe it makes sense, but we're just human!