Calculating Hours Worked and breaks taken

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
Hello

I thought I already posted this question but couldn’t find it anyways here I go

I am working on a weekly staffing schedule and it has the follow info type

C13 = start time ( 8:30 Am)
D13=End-time (5:00 PM)
E13 =Hours worked ( subtracting breaks with the following rules )

Works < 6 hours ( no break)
Works >8 hours (30 minute break)

Thanks
Ed
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
If they work less than 6 no break and if they work >7 30 minute break .. I was going to use >8 hours but it makes more sense to use 7
 

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
Anything over 6 hours gets a 30 minute break .. eg 6 Hours 1 minutes
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
A possibility could be .. but I know there is more simple one
=IF((D13-C13)*24*60> 6*60,(D13-C13)*24*60-30,(D13-C13)*24*60)/60/24
 

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
Thanks I’ll try that.. I hope it works thanks so much for taking the time
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Could not get this formula to work

What's the result or message
Here what I got

C13 = 08:30 AM
D13 = 5:30 PM
FA3 = 08:30 AM
 

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
I am not sure what you are referencing (FA3 = 08:30 AM what is this??) but when I apply the formula in cell E13 I am returning with 0.35416667

My spreadsheet is laid out as follows: C13 = 08:30 AM
D13 = 5:30 PM
F13 = ( 0.35416667) should look like (Hours worked - break )
or 9 hours worked - 0:30minutes = 8hours 30 minutes
 

Forum statistics

Threads
1,089,489
Messages
5,408,565
Members
403,215
Latest member
DblDocWhitaker

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top