Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-23-2017, 01:22 PM
AlanBird AlanBird is offline Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Windows 10 Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Office 2016
Novice
Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu
 
Join Date: Nov 2017
Posts: 2
AlanBird is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Rota main.xlsx (94.0 KB, 3 views)
Reply With Quote
  #2  
Old 11-24-2017, 06:15 AM
NBVC's Avatar
NBVC NBVC is offline Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Windows 10 Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 214
NBVC will become famous soon enough
Default

try...

=(D14-B14)*24-IF((D14-B14)*24>=6,0.5,0)
Reply With Quote
  #3  
Old 11-25-2017, 01:31 PM
joeu2004 joeu2004 is offline Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Windows 7 32bit Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Office 2007
Novice
 
Join Date: Aug 2016
Posts: 22
joeu2004 is on a distinguished road
Default

=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.
Reply With Quote
  #4  
Old 11-25-2017, 03:11 PM
AlanBird AlanBird is offline Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Windows 10 Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu Office 2016
Novice
Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu
 
Join Date: Nov 2017
Posts: 2
AlanBird is on a distinguished road
Default

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.
Reply With Quote
Reply

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
Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu If A1 greater than or equal to 1 Then A3 = A2*A1 brians Excel 5 03-09-2010 12:04 PM


All times are GMT -7. The time now is 01:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft