Microsoft Office Forums Formula to deduct a figure the sum of two cells is equal to or greater than a certain valu
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-23-2017, 01:22 PM
 AlanBird Windows 10 Office 2016 Novice Join Date: Nov 2017 Posts: 2
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
 Rota main.xlsx (94.0 KB, 3 views)
#2
11-24-2017, 06:15 AM
 NBVC Windows 10 Office 2013 The Formula Guy Join Date: Mar 2012 Location: Mississauga, CANADA Posts: 214

try...

=(D14-B14)*24-IF((D14-B14)*24>=6,0.5,0)
#3
11-25-2017, 01:31 PM
 joeu2004 Windows 7 32bit Office 2007 Novice Join Date: Aug 2016 Posts: 22

=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
 AlanBird Windows 10 Office 2016 Novice Join Date: Nov 2017 Posts: 2

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Redsox Excel 3 08-25-2017 08:54 AM msnarayanan Excel 2 02-25-2016 08:21 AM bm868 Excel 2 01-02-2016 10:29 AM matthew544 Excel 5 09-18-2011 08:56 AM brians Excel 5 03-09-2010 12:04 PM

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

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top