Search results

Two ways: 1. Paste the data into Table1 (cell B7 of the BUILD INFO sheet) overwriting what's already there and ensuring the new data is completely covered by the table extents and that all the old data is gone. Resizing of the table mostly happens automatically, but if not, you can adjust the...

So you think that 13:58:40 with 1 min 20 secs before 14:00 is closer to 14:00 than 13:58:57 with 1 min 03 secs before 14:00? I'm sure there's a world somewhere where you're right.

Raw data: Which is the closest before 14:00?: I say: You say:

In the attached: On sheet SCHEDULE, a table at cell M7 which you can adjust. It contains the start times of breaks and handover times in the first column and the duration of such events in the next column. A handover has 0 duration. On sheet BUILD INFO: A table at cell B6 which is your raw data...

Not tested, change .Range to .Databodyrange

See attached, result table at cell N2. No attempt made to make it 'elegant'!

Various solutions from other responders in columns B:E. This should translate to Italian formulae when you open it.

Recognises it here: after clearing the cell causing the SPILL error:

Did it work?

I'd guess, unlike the dataset you gave, that there are some Project IDs without a dot at all, truly top level ones. To debug, you can enter parts of the equation into a cell, eg. =TEXTBEFORE($A$2:$A$982,".")=TEXTBEFORE($A2,".") to get the full column of results. You can change the $A2 to another...

Test: in a cell in row 2: =IF(MIN(IF(TEXTBEFORE($A$2:$A$31,".")=TEXTBEFORE($A2,"."),$B$2:$B$31))=$B2,"top level", "") copy down.

The thread is fine here. I just wonder how many people will respond, because in that workbook's Module1 there are some 1300 lines of vba, 720 of which are actual code. I don't know who will be prepared to sift through it to get you want you want. There have been 8 views of that workbook so far…

In the file I attached in my last message I included a solution which will work for Excel 2021 (and Excel 2013). The formulae are in the yellow highlighted cells C11 and B12:C13. You need to move range B11:C13 to B3:C5 (overwriting what's already there), then you can delete all but the Holidays...

This sort of thing might be interesting to the likes of credit card companies, or those paying off debt/credit. People don't always pay the entire outstanding every month and the credit card companies charge different rates of interest at different times for various outstanding amounts...

looks like @Deepak_S lost interest…

ws.Range("A1:A" & lastRow).AutoFilter Field:=1, _ Criteria1:=">=" & CLng(DateSerial(recentYear, recentMonth, 1)), _ Criteria2:="<" & CLng(DateSerial(recentYear, recentMonth + 1, 1))

It might depend on how you want the scale to read: If like the blue line, one of AliGW's formulae [or =ABS(C6:C366/6-30)-15 ], if like the orange line then in D6: =COS(C6:C366*PI()/180)*15 or in older versions of Excel: =COS(C6*PI()/180)*15 copied down.

Try: To replace current formula in H9 (=C9): =SEQUENCE(,16,C9,(D9-C9)/15) and delete the formula =D9 in cell W9 You can copy this formula down one cell to get the percentages. In cell X9: =SEQUENCE(,15,D9+(E9-D9)/15,(E9-D9)/15) copy down. Delete the formulae in column AL I do not understand...

=TAKE(DROP(TEXTSPLIT(A1,","),,-1),,-1) or =LET(a,TEXTSPLIT(A1,","),INDEX(a,COUNTA(a)-1))

I'm having great difficulty understanding how you reach your Table-2 figures. So the following is a guess: I'm taking your invoices in date order, earliest first, then I'm looking for payments made after that invoice date and using them to pay that invoice, using as many payments as is needed to...

Is this correct?: Date Invoice Amount Payment received Amount Balance 21/03/2022 90,098.00 90,098.00 03/11/2022 -106,376.00 -16,278.00 14/11/2022 1,467,993.00 1,451,715.00 16/06/2023 -56,872.00 1,394,843.00 11/07/2023 -1,595.00 1,393,248.00 25/09/2023 50,951,006.00...

In D2 of Details sheet (also in D1 add the header, whatever you want (Flag?)): =IF(ISNUMBER(MATCH(A2,Students!$A$2:$A$4,0)),1,"") copy down.

Try =MATCH(A1-0.00001,{-0.00001,0.2,0.4,0.6,0.8,1},1)

=XMATCH(A1-0.00001,{-0.00001,0.2,0.4,0.6,0.8,1},-1)

See attached. Click on cell F2 and choose a Month/Year, refresh the results table. If you choose a month/year in the future and there is no data for the month-to-date or for that quarter those columns will not show in the results table. If you want to see those columns with 0 values you need to...