Screen Shot 2020-04-01 at 1.36.34 am.pngHi
I need help I'm trying to subtract 30min if time is above 7.5 hours and add $14 to m1 cell if above 8 hours. Here is a picture of what I have so far
which was for if it was above 5.5 hours however if I change it to 7.5 it doesn't work
would you attach a sample spreadsheet - showing examples with results expected
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Administrative note
Welcome to the forum
in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?
Please take a moment to read it and attach a sheet accordingly.
Thanks you for helping us help you
sorry there was a mistake I have uploaded a picture
rather than a picture can you load a spreadsheet , much easier to see whats going on and also to provide an answer in the context of your spreadsheet, otherwise we have to create the spreadsheet just to give an answer which is additional work
I have attached a file however it doesn't show on this thread
attachement
using your formula
=IF(MOD(D6-C6,1)>=5.5/24,MOD(D6-C6,1)-0.5/24,MOD(D6-C6,1))
and changing from 5.5 to 7.5
=IF(MOD(D6-C6,1)>=7.5/24,MOD(D6-C6,1)-0.5/24,MOD(D6-C6,1))
seems to work OK in column E
which bit does not work for you ?
it doesnt work it ? it still has same value for example cell E7 is 9:30 hours before formula and after it should be 9 however still remains same
C7 is 14:30
D7 is 00:30
so thats 10 hours
E7
Result is 9.5 regardless of >5.5 or >7.5
removes .5
sorry don't understand - can you explain
why should the result be 9 in row 7
how do I add $14 to cell M2 if above 8 hours
you have 1 cell but more than 1 row of data
do you want $14 if any of those rows are 8 hours or above
or a multiple - for each 8 hours add an additional $14
=COUNTIF(F5:F11,">"&8)
this will count how many entries above 8
so you could use
=COUNTIF(F5:F11,">="&8) * 14
if a multiple
OR
if just 1 payment regardless of how many days above 8
=IF(COUNTIF(F5:F11,">="&8)>0,14,0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks