

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 =(D14B14)*240.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...
=(D14B14)*24IF((D14B14)*24>=6,0.5,0) 
#3




=MOD(D14B14,1)*24  0.5*(MOD(D14B14,1)*24 >= 6)
The term MOD(D14B14,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 D14B14 is zero, that will be interpreted as zero time, not 24 hours. 
#4




The term MOD(D14B14,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 D14B14 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  08252017 08:54 AM 
formula using equals/greater than etc signs  msnarayanan  Excel  2  02252016 08:21 AM 
Need help with excel formula greater than..  bm868  Excel  2  01022016 10:29 AM 
Automatically change the value of one cell so that two other cells become equal  matthew544  Excel  5  09182011 08:56 AM 
If A1 greater than or equal to 1 Then A3 = A2*A1  brians  Excel  5  03092010 12:04 PM 