# Solved: #value!

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

#### 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

Zack said:
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

willow0ne said:
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

Zack said:
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!

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On

### Welcome to Tech Support Guy!

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 807,865 other people just like you!