# Calculating time, potential lunch break

1. ## 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.

2. 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. Try

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

format as time

4. 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. 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.....

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

7. 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. 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. daddylonglegs, you've nailed it, thank you very much.

Cheers for your help also gary.

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

#### 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 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (9)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (9)postbit_legacy
• (9)postbit_onlinestatus
• (9)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
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
• ./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_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_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• 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