Search results

Well, it DOES work in the sample file!!! I can't provide and test solutions for what I can't see. AliGW on MS365 Beta Channel (Windows 11) 64 bit TU 422:00 500:00 600:00 700:00 800:00 900:00 100:00 110:00 120:00 130:00 1455:00 1588:00 1644:00 1722:00 1800:00 Sheet: Schedule (2)

In K5: =IFERROR(INDEX(C:C,SMALL(IF(($B$5:$B$600="IH")*($H$5:$H$600>0),ROW($B$5:$B$600)),ROWS($1:1))),"") Confirm using CTRL+SHIFT+ENTER (not just ENTER) and then copy down.

You have seen what I posted earlier this morning. Please confirm that this did what you want (don't leave it hanging without any acknowledgement, please).

This seems to work in the latest sample workbook (and is my last contribution): =IF(N4-INT(N4)=(L4/24),0,IF(AND(N4-INT(N4)>G4-INT(G4),G4-INT(G4)>=6/24),0,IF(G4>N4-INT(N4),MAX(N4,22/24)-MAX(G4,22/24),0))) It's up to you to tweak it further if you find circumstances that fail - this is for your...

Do you want to go with @vletm and completely redesign this? If so, I won't waste my time creating anoher complex formula that has to overcome your layout and data defects.

Explain your outcome of 5 for row 14 - what are your calculation steps? Logic and maths, please.

I really don't know what you mean. I gave formulae for BOTH parts in post #18 and the OP said they worked in post #20. As far as I can see, I have fully solved the OP's issue (without improving the process). But as I said in my last post to the OP:

You're welcome. I have no idea what any of these statements mean, but I think that @vletm is probably trying to encourage you to change your approach to this. My formulae help you to get round failings in your data as it stands, which is far from optimal: going forward, you'd do well to follow...

ND: =IF(A3<6/24,MIN(6/24,D3-INT(D3))-A3,IF(A3>D3-INT(D3),(1-MAX(22/24,A3))+MIN(6/24,D3-INT(D3)),0)) NDOT...

ND: =IF(A3<6/24,MIN(6/24,D3-INT(D3))-A3,IF(A3>D3-INT(D3),(1-MAX(22/24,A3))+MIN(6/24,D3-INT(D3)),0))

Please share a sample workbook with some expected outcomes mocked up manually.

I don't understand your expected results. How do you arrive at 4 in J3? Explain your logic and maths, please.

Yes! That's what I asked for! Add extra columns in the sample foile with your manually calculated expected results (NOT non-working formulae).

You have formulae in place, so I don't know what you want assistance with - sorry. What is wrong with your current formulae? Highlight where you preceive that there are errors and tell us the result that you are expecting instead.

In the attached I have broken the formula down to show what each bit of it does. Please read the information carefully and ask if anything isn't clear.

The problem is, though, that the solution you found doesn't work fully - which is why you are asking here. I did spend some time trying to make it work for you, but could not, which is why I offered the more complex and newer approach, which I am happy to explain to you if you wish. Take this...

You forgot to format the column as TIME! If this isn't what you want, then you need to provide your expected results.

Do you want an explanation so that you can learn, or are you just going to dismiss this solution because you don't want to be bothered with learning new tricks? If the latter, then I'll step aside and leave it to someone else. The solution matches the rows, but there is no need to match columns...

In AF2 copied down: =IF(B2<A2,(1-MAX(22/24,A2))+MIN(6/24,B2),0) You can work out NDOT yourself from this, as I am not sure what you want exactly.

Your question is too vague to answer fully. It might be possible with VBA (I can't help you with this), but it's not at all clear what you are expecting to happen. Provide a sample workbook with before and after mocked up.

Further, here's a scalable version: =DROP(REDUCE("",B3:B10,LAMBDA(x,y,VSTACK(x,BYCOL(FILTER(VSTACK(Start:Finish!C4:N500),(VSTACK(Start:Finish!A4:A500)=y)*(VSTACK(Start:Finish!B4:B500)="USD")),LAMBDA(c,SUM(c)))))),1) I added two empty sheets: Start and Finish. Make sure that ALL source sheets...

Are you using 365? If so, try removing ALL expected results from the months columns of the grid and paste this into C3 followed by ENTER...

You can't control text formatting with a formula.

Yes, my post has been ignored, so I'll repeat my request. Can you share a desensitised workbook? Workbook, NOT screenshot. Thanks.

Can you share a desensitised workbook?