formula for work rosters to deduct for different breaks
From hours rostered on a spreadsheet I need to deduct 30 mins for a break if
staff workd 6 hours or more, and deduct 60 mins for a break if staff work 8 hours or more |
formula for work rosters to deduct for different breaks
With time in cell A1 in excel time format; try the below formula and format
to time =IF(A1=TIME(8,0,0),A1-TIME(0,60,0),IF(A1=TIME(6,0,0),A1-TIME(0,30,0),0)) If this post helps click Yes --------------- Jacob Skaria "vefl" wrote: From hours rostered on a spreadsheet I need to deduct 30 mins for a break if staff workd 6 hours or more, and deduct 60 mins for a break if staff work 8 hours or more |
formula for work rosters to deduct for different breaks
=A1-LOOKUP(A1,{0,6,8},{0,0.5,1})
"vefl" wrote: From hours rostered on a spreadsheet I need to deduct 30 mins for a break if staff workd 6 hours or more, and deduct 60 mins for a break if staff work 8 hours or more |
formula for work rosters to deduct for different breaks
Are values in hours or number format or combined date time?
Since you are talking about rostering, is it at all possible you may need to do this with times that span midnight? Why? =B2-A2 will work if A2 is 06:00 and B2 anything up to midnight. If B2 is 23:00 and A2 is 05:00, you have a problem to solve. Your formula will become more complex and difficult to understand. A little more detail may help produce a better solution for you. Lil -- ?B?dmVmbA==?= wrote in : From hours rostered on a spreadsheet I need to deduct 30 mins for a break if staff workd 6 hours or more, and deduct 60 mins for a break if staff work 8 hours or more |
All times are GMT +1. The time now is 08:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com