Pleeaasee help. I am new to excel. I am trying to make a work schedule (roster) that has there weekly shift times wed-tues. I'm wanting my total column to equal hours worked for the week minus 30 unites if worked over 5hours for a shift. I have it calculating the hours for the week hoping there is a way to do it with the half taken off for each day over 5. If that makes sense.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Total hours minus 30 minutes
- Thread starter dandel90
- Start date
Kaper
Well-known Member
- Joined
- Mar 14, 2014
- Messages
- 608
- Office Version
- 365
- Platform
- Windows
So everyone who is working more than 5:00 will have 30 minutes less counted?
Am I right? like: 6:15->5:45 ; 8:00 -> 7:30 and ... 5:15->4:45?
if it is the case, then use such approach:
or shorter:
By your formula i mean a formula used to calculate time worked each day (You probably calculate it as a difference of Time_out and Time_in each day.
Or you can do this in final column, like you have in I2:
a sum of times for a whole week, then modified formula will be:
Am I right? like: 6:15->5:45 ; 8:00 -> 7:30 and ... 5:15->4:45?
if it is the case, then use such approach:
Excel Formula:
=if(your_formula>TIMEVALUE("05:00:00"),your_formula-TIMEVALUE("00:30:00"),your_formula)
Excel Formula:
=if(your_formula>5/24,your_formula-1/48,your_formula)By your formula i mean a formula used to calculate time worked each day (You probably calculate it as a difference of Time_out and Time_in each day.
Or you can do this in final column, like you have in I2:
Excel Formula:
=SUM(B2:H2)
Excel Formula:
=SUM(if(B2:H2>5/24,B2:H2-1/48,B2:H2))
Upvote
0
Similar threads
