+ Reply to Thread
Results 1 to 5 of 5

Hours scheduled gives #value if off work that day

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    Southern California
    MS-Off Ver
    Excel 2013
    Posts
    8

    Hours scheduled gives #value if off work that day

    I know that as soon as I get some help with this I am going to kick myself because I should know this

    On a work schedule I have a calculation that I do to calculate the hours worked. Just a simple =SUM(C19-B19) [Time out -time in] I then take this result to deduct a lunch break if necessary and add all of the hours together to make sure I am under payroll. The issue I am having is that when I write "OFF" or "VAC" to denote a day off of vacation day I get a #value since there is obviously no numbers there and I forget how to tell excel to ignore those cells without numbers


    here is my set up

    B19 C19 ...off to the side ... =SUM(C19-B19)
    Time in Time out

    So if I write off in either cell I get errors

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Hours scheduled gives #value if off work that day

    Hi,

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know how this goes

  3. #3
    Registered User
    Join Date
    08-22-2013
    Location
    Southern California
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Hours scheduled gives #value if off work that day

    That is exactly what I was looking for, Thank you!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Hours scheduled gives #value if off work that day

    Hi and welcome to the forum

    for a start, for a simple addition like this, you dont need to use =sum()
    to fix this problem, use the iferror() function...

    =iferror(C19-B19,"")
    you can replace the "" with anyt6hing you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Hours scheduled gives #value if off work that day

    You're welcome!

    Happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate hours a person is scheduled to work.
    By Radical_Magic63 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-23-2012, 10:29 AM
  2. Total the hours scheduled
    By kabshire4608 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-06-2010, 03:59 PM
  3. scheduled hours
    By robert220 in forum Excel General
    Replies: 6
    Last Post: 07-29-2008, 07:45 PM
  4. scheduled hours total
    By Jeff Desruisseaux in forum Excel General
    Replies: 1
    Last Post: 07-01-2005, 07:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1