+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Rounding hours up and down for 15minute clock in/out intervals?

  1. #1
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    351

    Rounding hours up and down for 15minute clock in/out intervals?

    Anyone able the two required formulas in row 5 & 7 in the attached?

    Hours Totals & Rounding.xlsx

    Clock IN's, need to be rounded UP to the nearest 15 minute interval
    Clock OUT's, need to be rounded DOWN to the nearest 15 minute interval

    Thanks

  2. #2
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Rounding hours up and down for 15minute clock in/out intervals?

    I haven't opened your file, but:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will always round down to the nearest 15 mins, and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will always round up.

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Rounding hours up and down for 15minute clock in/out intervals?

    Hi benoj2005,

    Try using below formula:-

    =MROUND(I16,0.0104166666666667)

    where I16 cell has the time

    See attached:- Hours Totals & Rounding.xlsx


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  4. #4
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Rounding hours up and down for 15minute clock in/out intervals?

    Slight amendment:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    is required if the time to be rounded down is only a few minutes after midnight.
    Last edited by stunn; 07-17-2012 at 04:24 AM. Reason: cell reference was in r1c1 format

  5. #5
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    351

    Re: Rounding hours up and down for 15minute clock in/out intervals?

    Quote Originally Posted by stunn View Post
    Slight amendment:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    is required if the time to be rounded down is only a few minutes after midnight.
    That causes the total for the day to change to #####

    see here Hours Totals & Rounding.xlsx

  6. #6
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    351

    Re: Rounding hours up and down for 15minute clock in/out intervals?

    Quote Originally Posted by dilipandey View Post
    Hi benoj2005,

    Try using below formula:-

    =MROUND(I16,0.0104166666666667)

    where I16 cell has the time

    See attached:- Attachment 168575


    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    That works for clock IN's but not for Clock OUT's, which need to round DOWN, not UP

  7. #7
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Rounding hours up and down for 15minute clock in/out intervals?

    You have 00:00-15:15, which may look fine to us humans, but for Excel it results in a negative time, which it cannot handle. To get around this, you would have to add 1 day when "Interval Out" is greater than "Interval In". i.e. when the shift goes over into the next day. Try this in C9:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and, of course, this in C10:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by stunn; 07-17-2012 at 04:50 AM. Reason: removing excess brackets

  8. #8
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    351

    Re: Rounding hours up and down for 15minute clock in/out intervals?

    Quote Originally Posted by stunn View Post
    You have 00:00-15:15, which may look fine to us humans, but for Excel it results in a negative time, which it cannot handle. To get around this, you would have to add 1 day when "Interval Out" is greater than "Interval In". i.e. when the shift goes over into the next day. Try this in C9:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and, of course, this in C10:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks, that worked.

    You'd think they'd have some functions for dealing with time like this.

    All sorted, solution here if anyone wants for future Hours Totals & Rounding.xlsx

+ 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06694 seconds
  • Memory Usage 9,099KB
  • Queries Executed 15 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (3)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (8)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (8)postbit_legacy
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (46):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1