1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: #value!

Discussion in 'Business Applications' started by willow0ne, Oct 4, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. willow0ne

    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
     
  2. bomb #21

    bomb #21

    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)
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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!
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Yeah, well ... you even drive on the wrong side. :p

    Good to "see" you. :)
     
  5. willow0ne

    willow0ne Thread Starter

    Joined:
    Mar 3, 2008
    Messages:
    9
    I do have shifts that go past midnigt. Do you know of a alternative formula?
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. ;)
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    =(C21-B21)+(C21<B21) seems to ring a bell.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    But why double the formula demand when you just have to use the right input value/format?
     
  9. bomb #21

    bomb #21

    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:
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  11. bomb #21

    bomb #21

    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. :)
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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 :)
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. ;)
     
  15. slurpee55

    slurpee55

    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! ;)
     
  16. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/756105

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice