Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 26th 15, 11:40 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2015
Posts: 1
Default Calculating my work hours minus lunch break and then adding forweekly total

I'm having trouble creating a time worksheet. I want to subtract my start time from my end time, determine if I have worked longer than 4 hours (company automatically takes 30 mins for a lunch break off total if you work more than 4 hours), and then calculate total house worked. I'm having a problem with the lunch break portion of the calculation. I need a formula that will work. What can I do??

  #3   Report Post  
Old March 28th 15, 05:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2015
Posts: 4
Default Calculating my work hours minus lunch break and then adding forweekly total

bobbethel schrieb am 26.03.2015 04:40:36 mit Betreff "Calculating my
work hours minus lunch break and then adding for

weekly total":
I'm having trouble creating a time worksheet. I want to subtract my start time from my end time, determine if I have worked longer than 4 hours (company automatically takes 30 mins for a lunch break off total if you work more than 4 hours), and then calculate total house worked. I'm having a problem with the lunch break portion of the calculation. I need a formula that will work. What can I do??


If I got you right, your company wants you to take a break of at least
30 minutes within the first 4,5 hours.
If you work 4:10 hours before lunch, then you loose 10 minutes, not half
an hour, right?

I have similar conditions for breaks at work and I use the following
since years.


Say, you note your working time in "From-To"-pairs in cells A2 till F2.
You can note up to three working-periods.
In G2 note the following formula:
=IF(MAX(0,F2-E2)+MAX(0,D2-C2)+MAX(0,B2-A2)<=4/24,MAX(0,F2-E2)+MAX(0,D2-C2)+MAX(0,B2-A2),MAX(4/24,MAX(0,F2-E2)+MAX(0,D2-C2)+MAX(0,B2-A2)+MIN(0,MAX(0,MIN(A2+4,5/24,MAX(B2,C2))-B2)+MAX(0,MIN(A2+4,5/24,MAX(D2,E2))-D2)-0,5/24)))

It gives, for example, the following results:

From_1 To_1 From_2 To_2 From_3 To_3 Working hours*)
08:00 10:00 10:00 11:00 03:00
08:00 12:05 12:30 14:00 05:30 (5 min lost)
08:00 15:00 06:30 (30 min lost)
08:00 09:00 10:00 18:00 09:00 (lunch break:9-10)
08:00 09:00 09:15 14:00 14:30 17:00 08:00 (15 min lost)
08:00 09:00 09:15 09:20 09:25 13:00 04:30

*) Working hours with break of 0:30+ after at most 4 hrs

The formula can be simplified, if you never use From_3, To_3 ([E3], [F3]).

Regards,
Alfred


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need to calculate hours worked from 11pm to 7am minus a lunch Paula Brooks Excel Worksheet Functions 2 June 2nd 09 05:07 PM
Calculate hours worked minus a half hour for lunch Danedel Excel Worksheet Functions 3 August 29th 08 01:12 AM
how to calculate hours worked daily & minus 30 min lunch, 7am-5pm Debby_Jo Excel Discussion (Misc queries) 1 July 23rd 08 03:40 AM
How can I calculate daily hours worked minus lunch to eual a decim lili Excel Worksheet Functions 6 March 28th 07 03:28 AM
Calculating total work week hours Harley mom Excel Worksheet Functions 2 December 20th 05 05:41 PM


All times are GMT +1. The time now is 11:58 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017