|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu
I have a spreadsheet that is used as a rota sheet and it calculates the length of a team members shift using =(D14-B14)*24-0.5 and then this is displayed in cell D16 as the total length of the shift less 0.5 hours for a break. I need the 0.5 to be deducted only if the team member works 6 or more hours. I have not been able to work out the formula, please can someone help? I would like the result to be shown in D16.
Occasionally shifts will run past midnight. |
#2
|
||||
|
||||
try...
=(D14-B14)*24-IF((D14-B14)*24>=6,0.5,0) |
#3
|
|||
|
|||
=MOD(D14-B14,1)*24 - 0.5*(MOD(D14-B14,1)*24 >= 6)
The term MOD(D14-B14,1) handles the case where the start time (B14) is one day and the end time (D14) is the next day ("runs past midnight"). Note: if D14-B14 is zero, that will be interpreted as zero time, not 24 hours. |
#4
|
|||
|
|||
The term MOD(D14-B14,1) handles the case where the start time (B14) is one day and the end time (D14) is the next day ("runs past midnight"). Note: if D14-B14 is zero, that will be interpreted as zero time, not 24 hours.[/QUOTE]
Thank you that worked perfectly. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can't figure out a formula - Vlookup or something else? | Redsox | Excel | 3 | 08-25-2017 08:54 AM |
formula using equals/greater than etc signs | msnarayanan | Excel | 2 | 02-25-2016 08:21 AM |
Need help with excel formula greater than.. | bm868 | Excel | 2 | 01-02-2016 10:29 AM |
Automatically change the value of one cell so that two other cells become equal | matthew544 | Excel | 5 | 09-18-2011 08:56 AM |
If A1 greater than or equal to 1 Then A3 = A2*A1 | brians | Excel | 5 | 03-09-2010 12:04 PM |