+ Reply to Thread
Results 1 to 9 of 9

Calculating time, potential lunch break

  1. #1
    Registered User
    Join Date
    06-07-2007
    Posts
    21

    Calculating time, potential lunch break

    Hi All

    I'm struggling with a formula.

    I am creating a worksheet where employees schedule work throughout the day
    eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.

    The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
    eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.

    I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.

    I hope someone can help.
    PS, it needs to be a formula and not vba as we have a problem with macro's if multiple people use the worksheet.
    Last edited by B1123; 10-13-2008 at 09:19 AM.

  2. #2
    Forum Contributor
    Join Date
    12-02-2004
    Location
    London
    Posts
    255
    The formual below assumes the start time is in cell A1 and the end time is in B1. It also assumes that if the start time or end time fall between 1 and 2, then 1 hour is deducted, I'm not sure this is true if the start time is 1:30pm?

    Here is the formula:-

    =IF(OR(AND(A1>=TIME(13,0,0),A1<=TIME(14,0,0)),AND(B1>=TIME(13,0,0),B1<=TIME(14,0,0))),(B1-A1)-TIME(1,0,0),IF(AND(A1<TIME(13,0,0),B1>TIME(14,0,0)),(B1-A1)-TIME(1,0,0),B1-A1))

    Gary

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Try

    =IF(COUNT(A1,B1)=2,B1-A1-AND(A1<="13:00"+0,B1>="14:00"+0)/24,0)

    format as time

  4. #4
    Registered User
    Join Date
    06-07-2007
    Posts
    21
    Thank you both

    Both formula's seem to work EXCEPT I have the same problem in both

    If I put a task in from 1pm to 3pm your calculations are correct i get 1 hour yippee

    However if I put in a task from 2pm to 3pm it is now coming up with 0 hours, this is incorrect as lunch is 1pm-2pm. I tried to play with both formula's to say 1:59:00 but this is not working.

    Any advice would be hugely grateful

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    If you have 14:00 in A1 and 15:00 in B1 then I believe that the formula I suggested will correctly return 1:00......but here's a better formula

    =IF(COUNT(A1,B1)=2,B1-A1-MAX(0,MIN(B1,"14:00")-MAX(A1,"13:00")),0)

    It counts all hours between the two times except those between 13:00 and 14:00, even if start or end times are between those 2, e.g. 13:20 to 14:20 will give a result of 0:20.

    Formula only works when A1 and B1 are on the same day, i.e. B1 is greater than A1.....

  6. #6
    Registered User
    Join Date
    06-07-2007
    Posts
    21
    Hi Daddylonglegs

    Still not working,

    I have attached a sample of my doc for you to look at, D13-D16 are the actuals, reading 1,2,3,1 hours respectively.

    I need to apply a formula in e13-16 excluding lunch between 1pm and 2pm so the results should be 1,2,2,1 respectively

    I hope you can help
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-02-2004
    Location
    London
    Posts
    255
    Amend my formula as follows:-

    =IF(OR(AND(A1>=TIME(13,0,0),A1<TIME(14,0,0)),AND(B1>=TIME(13,0,0),B1<=TIME(14,0,0))),(B1-A1)-TIME(1,0,0),IF(AND(A1<TIME(13,0,0),B1>TIME(14,0,0)),(B1-A1)-TIME(1,0,0),B1-A1))

    Regards

    Gary

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You can adjust my formula to your specific cell references and multiply by 24 to get decimal hours, i.e. in D13 copied down

    =IF(COUNT(B13,C13)=2,(C13-B13-MAX(0,MIN(C13,"14:00")-MAX(B13,"13:00")))*24,0)

    format cell as number

    Note: that your times all appear to be 1 second past the hour, I don't know if that's deliberate, so for row 15 my formula will give you the number of hours between the end of lunch, i.e. 14:00:00 and the end time in C15 16:00:01, i.e 2 hours and 1 second which converts to approx 2.000278

  9. #9
    Registered User
    Join Date
    06-07-2007
    Posts
    21
    daddylonglegs, you've nailed it, thank you very much.

    Cheers for your help also gary.

+ 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. Code Execution Degrades Over Time
    By KDT in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-25-2008, 04:28 PM
  2. Calculating a percentage of a given time span
    By Jericho in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2008, 06:35 PM
  3. Time Ranges
    By jmag in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-17-2008, 03:24 PM
  4. Calculating time between process steps
    By TK5 in forum Excel General
    Replies: 3
    Last Post: 03-17-2008, 11:27 AM
  5. calculating time values
    By toivo112 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-07-2008, 01:21 PM

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.06747 seconds
  • Memory Usage 9,087KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (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
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (9)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (9)postbit_legacy
  • (9)postbit_onlinestatus
  • (9)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (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 (49):
  • 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
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1