+ Reply to Thread
Results 1 to 8 of 8

Trunc function

  1. #1
    Registered User
    Join Date
    05-11-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Trunc function

    Hi,

    I have a timesheet that has been working perfectly but something has happened and I'm not sure what it is. The problem is that any hours entered that equals 1.3, 2.3,3.3,4.3 (anything that ends with (.3)) is recording at .7. I use excel 2003.

    Could someone please help me with this?
    Last edited by vbadummy; 05-26-2009 at 05:04 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Trunc Function

    Does this fix it?

    Please Login or Register  to view this content.
    check with other data.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Trunc Function

    What is the formula supposed to do?
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    05-11-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Trunc Function

    NBVC, the formula works perfectly!! Would you have any ideas why the original formual all of a sudden went wrong?
    Thanks for all of your help, this forum is fantastic!!!!

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Trunc Function

    It's not clear from a single example what the formula should do. Like shg says, can you explain, perhaps with 5 or 6 representative examples and the results you want.

    The complicated formula you are using can almost certainly be simplified

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Trunc Function

    Quote Originally Posted by vbadummy View Post
    NBVC, the formula works perfectly!! Would you have any ideas why the original formual all of a sudden went wrong?
    Thanks for all of your help, this forum is fantastic!!!!
    Well, if that works, then it seems to be dependent on the sign of the result of this part of your formula:

    (TRUNC(C21)-TRUNC(C20))*40+(C20-C21)*100

    so if negative, it subtracts, if positive it adds to the front end of the formula..

    The MOD() function on it's own always returns a positive number.. so before you were always adding...the SIGN() function allows you to determine the sign of the result and multiply that by your Mod() result.. and then add to the front end... adding -1*... actually subtracts....

  7. #7
    Registered User
    Join Date
    05-11-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Trunc Function

    Hi, I thought that the been fixed but I've just come across a new problem...
    Last edited by vbadummy; 05-26-2009 at 05:07 AM.

  8. #8
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Trunc Function


    I beg your pardon, but in my opinion if you had used time formats, like 5:45 instead of 5.45, you wouldn't have had this issue and so long incomprehensible, I would even say preposterous, formula.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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