+ Reply to Thread
Results 1 to 5 of 5

time round off formula

  1. #1
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    time round off formula

    Dear members,

    i am having a time in format 03:22:42 in one cell.

    i want to round off the seconds to 03:22:00
    also i want to round off the minutes to 03:20:00 or 03:25:00

    pls guide me.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: time round off formula

    You can use this for first one... =E2-TIME(,,SECOND(E2))

    And this for second =MROUND(E4,"00:05:00")

    For MROUND function if you haven't already done so, install the Analysis Toolpack add-in.
    (Tools > Add-Ins...)
    Last edited by zbor; 02-19-2011 at 04:52 PM.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: time round off formula

    Alternatively

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: time round off formula

    Or this for second one....

    =MOD(E2-TIME(;;SECOND(E2));5)

  5. #5
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: time round off formula

    Quote Originally Posted by zbor View Post
    Or this for second one....

    =MOD(E2-TIME(;;SECOND(E2));5)
    pls reconfirm this formula as i am not getting the correct result.

+ 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