+ Reply to Thread
Results 1 to 9 of 9

Do not display calculated cell value (Time format) in timesheet

  1. #1
    Registered User
    Join Date
    04-18-2011
    Location
    Slovakia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Do not display calculated cell value (Time format) in timesheet

    Hi guys...

    once again I would be really happy if someone could help me...

    I have timesheet, there is TIME IN and OUT and calculated hours...then it calculate overtime...but I need to hide zero value in overtime cell...Ive tried couple of formulas but nothing works for me... does anyone has a clue how to do this..

    Here is sample timesheet timesheet_ex.xlsx (green is display, red is hide)

    Thanks in advance
    Last edited by kocco; 04-11-2012 at 07:49 AM.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Do not display calculated cell value (Time format) in timesheet

    Try this, your values of 0 are not equal to exactly 0, so you need to check for anything less than a very small value

    =IF((J6-L6)<0.0001,"",(J6-L6))
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Do not display calculated cell value (Time format) in timesheet

    Try

    =IF(OR(J6=L6,ISERROR(J6-L6)),"",J6-L6)

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Do not display calculated cell value (Time format) in timesheet

    Use Bob's solution, i forgot to account for the error factor.

  5. #5
    Registered User
    Join Date
    04-18-2011
    Location
    Slovakia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Do not display calculated cell value (Time format) in timesheet

    Thank you for reply...but my excell is giving me error in formula...actually in both...

  6. #6
    Registered User
    Join Date
    04-18-2011
    Location
    Slovakia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Do not display calculated cell value (Time format) in timesheet

    Quote Originally Posted by kocco View Post
    Thank you for reply...but my excell is giving me error in formula...actually in both...
    I'm sorry guys...Bob's solution works!!!! Great...THANK YOU

  7. #7
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Do not display calculated cell value (Time format) in timesheet

    are you getting an error while entering the formula? this could be regional setting, try this:

    =IF(OR(J6=L6;ISERROR(J6-L6));"";J6-L6)

    if that is not the solution, could you please share the error?

  8. #8
    Registered User
    Join Date
    04-18-2011
    Location
    Slovakia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Do not display calculated cell value (Time format) in timesheet

    Yep...I tried exactly this and it worked...Thank you so much!!
    Quote Originally Posted by DGagnon View Post
    are you getting an error while entering the formula? this could be regional setting, try this:

    =IF(OR(J6=L6;ISERROR(J6-L6));"";J6-L6)

    if that is not the solution, could you please share the error?

  9. #9
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Do not display calculated cell value (Time format) in timesheet

    No problem, could you please mark this thread as solved?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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