Solved: #value!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

willow0ne

Thread Starter
Joined
Mar 3, 2008
Messages
9
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
 
Joined
Jul 1, 2005
Messages
8,546
=IF(B20="OFF",0,IF(C20-B20<=1/24*7,C20-B20,C20-B20-(1/48)))

(unless you have any shifts spanning midnight)
 
Joined
Jul 25, 2004
Messages
5,458
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!
 
Joined
Jul 1, 2005
Messages
8,546
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. :p

Good to "see" you. :)
 

willow0ne

Thread Starter
Joined
Mar 3, 2008
Messages
9
I do have shifts that go past midnigt. Do you know of a alternative formula?
 
Joined
Jul 25, 2004
Messages
5,458
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. ;)
 
Joined
Jul 1, 2005
Messages
8,546
Who, me? :D

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. :eek:
 
Joined
Jul 25, 2004
Messages
5,458
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.
 
Joined
Jul 1, 2005
Messages
8,546
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. :)
 
Joined
Jul 25, 2004
Messages
5,458
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. :)
 
Joined
Jul 1, 2005
Messages
8,546
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. :D :)
 
Joined
Jul 25, 2004
Messages
5,458
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. ;)
 
Joined
Oct 20, 2004
Messages
7,837
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. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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

Join over 807,865 other people just like you!

Latest posts

Staff online

Top