Search results

For example, if you have Excel 2021 or newer, this in H10: =LET(x,INDEX(C54:C69,MATCH($I1,$A54:$A69,0)), IF(ISNUMBER(MATCH(x,{"H","O","L","I","D","A","Y"},0)),"HOLIDAY",x))

Which version of Excel?

Try this: =SUMPRODUCT((TRIM($B$6:$B$51)=TRIM(F30))*$A$6:$A$51) or this: =SUMPRODUCT((TRIM($B$6:$B$51)="Paws")*$A$6:$A$51) BUT the correct way to deal with this is to cleanse your data, not work around it.

Did you write it correctly? =MINIFS(B1:B8,B1:B8,"<"&0) =MAXIFS(B1:B8,B1:B8,">"&0)

This sectopn is about PowerB!, PowerQuery and PowerPivot - in your opening post, you did not specify any of the three. Please provide a workbook - we cannot manipulate screenshots (they are of practically no use whatsoever). PowerQuery in Excel should behave in the same way as PowerBI, so you...

By the way - you have two Diff columns. All columns would need distinct names for anything to work. I am waiting for an updated sample workbook that properly reflects your real data layout. In the meantime, if headings are unique and match across workseets, then you could use this...

Why did you not provide more realistic sample data at the outset? If you provide a realistic layout, then I shall see what I can do. I am not gpoing to guess.

Do I really look like a Sir??? :confused: You entered the formula incorrectly. Remove ALL sample data from A2 onwards, then in A2 followed by ENTER (NOT CTRL+SHIFT+ENTER): =CHOOSECOLS(Sheet1!A2:K30,1,2,3,7,8,9) Do NOT copy down - the formula will spill by itself. No need.

Thanks for the like - is this now resolved?

If you have 2024 or 365, in A4 followed by ENTER: =CHOOSECOLS(Sheet1!A2:K30,1,2,3,7,8,9)

Which version of Excel? Is this a MOVE or COPY operation?

I used formatting to do the same and the dates are still being read as dates: AliGW on MS365 Beta Channel (Windows 11) 64 bit A B C D 1 Nov 24 2 Dec 24 31 =DATEDIF(A1,A2,"d") 3 31 =A2-A1 Sheet: Sheet1 Attach a workbook so that we can get to the bottom...

You're welcome. Feel free to LIKE my solution.

It is working in the sample workbook - did you look at the attachment?

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.