|
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
11-23-2017, 01:22 PM
|
|||
|
|||
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. 
|
|
#3
11-25-2017, 01:31 PM
|
|||
|
|||
|
=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
11-25-2017, 03:11 PM
|
|||
|
|||
|
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.
|
|
|
| Thread Tools | |
| Display Modes | |
|
|
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 |
Windows 10 
Office 2016
Linear Mode
