Formulas and Functions

25189 Topics

Calculate overlapping hours
Hello, I need to report how many hours a staff member supervised one or more volunteers. For a very simplified example, Volunteer Name Date Start Time End Time Supervisor Fred 1/1/26 1:00pm 3:00pm Lucy Ethel 1/1/26 2:30pm 4:30pm Lucy Here 4 volunteer hours were served, but because there was a 30 minute overlap, Lucy only supervised for 3.5 hours. Is there a way to get Excel to calculate that? To say: look at all the entries with matching date and matching supervisor, and add up non-overlapping time. I'm not expecting this to be possible, but I thought I'd ask. Thanks!
Solved
volunteersfpl
Dec 21, 2025 Place Excel
120Views
1like
9Comments
PivotBy or GroupBy
Dear Experts, Quick question, I have a data like this from Column A~F, and want to make a data like in Col J~L So, I want a summary , that for the slot0 && txnum==1, what are the unique values of numOfPrb, but in case of slot5, we have more than one unique value and it spills, could you please suggest a solution for this, I tried Filter function etc.. may be there's solution within , or if Groupby or Pivotby can help here. Attached is the worksheet, Br, Anupam
anupambit1797
Dec 21, 2025 Place Excel
34Views
0likes
1Comment
Non-Consecutive Cell Referencing
Hi, folks. I'm attempting to create a spreadsheet that contains links from consecutive cells to consecutive cells in another worksheet that are separated by 5 intervening cells. I'll call the original consecutive spreadsheet "Orig" (for original). So, I know that if I put "='Orig'!A3" in cell B3 and then copy that down, it will update the relative formula consecutively, i.e. B3='Orig'!A3, B4='Orig'!A4, B5='Orig'!A5, B6='Orig'!A6.... that much I get. What I need to do is find a way to do the same thing, but to increase the resulting link.....so that if I copied the formula down column B I would get: B3='Orig'!A3, B8=Orig'!A4, B13='Orig'!A6', etc so that the new worksheet is moving down 5 cells relative to the Orig sheet consecutive order. I've read where someone used a formula using the INDIRECT function but that's beyond my beginner level. Many thanks, and merry Xmas to all!
MattKW1
Dec 20, 2025 Place Excel
103Views
0likes
10Comments
Return of the Corkscrew with Spilled Arrays
I'm starting a new topic but do want to refer to PeterBartholomew1 post on accumulators here https://techcommunity.microsoft.com/t5/excel/ways-of-performing-accumulation-with-dynamic-arrays/m-p/2329035 This post was in 2021 and things have or may have moved on since then and I'm wondering if there is an easier way to achieve the following. The Corkscrew. I have seen a number of addins and other 5G functions which do far more than I want. I'm basically looking for a way to break the circularity in a corkscrew and it seems that if I can calculate the top row ie typically the opening balance without referring to the closing balance then all would be good. To add complexity I’m trying to fuse the actuals and forecasts in this single function. I have flags to determine the actuals and can use NOT actuals to get the forecasts (same flags) and can pic up the actual opening balance from the source data easily. As I will calculate all time periods for both I can then just add them (as actuals will be multiplied by the Actual flag and thus zero for forecast periods and vice versa for the forecasts. So I just need to calculate the forecast period which will be the same for the entire array block. The key is. getting the previous value. (for the inflows and outflows as they are already calculated I can just shift their arrays forward I col but adding a column in front to get the previous value so that is easy. I can then get the closing balance by adding the inflows and outflows that would fall in the middle of the corkscrew to this previous value to simulate an opening balance. The inflows and outflows can then be added easily and the closing balance simply summed. So all the clever work would go in the opening balance. The added complexity I have is that I am doing a multi row version. ie there are ‘currently’ 3 entities all with opening balances, all with inflows and outflows and closing balances. I want to calculate these in blocks of these entities – 1 row per entity but in a single dynamic array. But the corkscrew will be made up of 4 dynamic arrays – opening bal, inflows, outflows and closing. If someone has a better logic here then do let me know. I saw Jeff Robsons one from a few years back using sumifs for the middle bit. But again, I want to do these in separate blocks which does add a layer of complexity. The reason is that I can ( have) modelled with multiple entities together rather than separately which means one just needs to extend the blocks (VBA to the rescue as there will be a good number of them) as required and inputs can just be assigned to the respective entity (often locations around the world might just be a tiny rep office with hardly anything going on so no need for a fully blown model but can be detailed separately. I also have the ability to enable or disable entities so they are included or excluded from the calcs. SO back to the problem, Model attached. Blue cells mark the dynamic array function for the block. The inflows and outflows will come from other dynamic arrays. But this idea means that one can expand the middle if the top/bottom are taken care of with regard to the circularity. I’m trying to get something easy to implement ideally in a single function (with or without LET)
Solved
James_Buist
Dec 20, 2025 Place Excel
2.1KViews
0likes
34Comments
How to write a script or any PQ or in Excel to download the zip files from a Webpage
Dear Experts, Greetings! https://www.etsi.org/deliver/etsi_ts/138300_138399/138306/ Could you please help me on how to download the pdf.zip files from above for all the versions? Using a single command in Excel or PQ-option. Thanks in Advance, Br, Anupam
Solved
anupambit1797
Dec 19, 2025 Place Excel
204Views
1like
5Comments
Logical test for same text string existing anywhere in both ranges.
Hello. I have a Table of film credits, including the names of directors and writers. Some films have multiple directors (up to 3 individuals), whose names are in columns F, G and H. The writers' names (up to 4 individuals) are in columns J, K, L and M. I want to test for whether the film has a writer/director - e.g, one of the director names in the range F:H is the same as one of the writer names in the range J:M. I have created a column O to contain a formula with a logical test returning Y if there is a writer/director present. I tried =IF(Table4[@[Wri1]:[Wri4]]=[@Dir1]:[Dir3],Y,N) but this returns a spill error. Can anyone help?
Solved
Extopia
Dec 19, 2025 Place Excel
119Views
1like
8Comments
Finding time duration between a start date & time with end date & time
Hi all! I'm looking for any formula or power query to calculate a total time duration within a day, given the start date, start time, end date, end time. Most of the dates will equal the same but there are some with the end date being the next day. I'd like to be able to exclude any overlaps as well. Currently, I have a large embedded IF formula: =IF(AND($G4=$O4,$H4<$H5,$P4>=$H5,$P4<$P5,$H4<$H3),$P4-$H4,IF(AND($G4=$O4,$G4>$G3,$H4<$H3,$P4<$P3,$H4<$P3,$P4<$P5),$P4-$H4,IF(AND($G4=$O4,$H4>$H3,$H4>=$P3,$P4>$P3),$P4-$H4,IF(AND($G4=$O4,$H4=$P4),0,IF(AND($G4=$O4,$H4<$P3,$P4<=$P3),0,IF(AND($G4<$O4,$H4<$P3,$P4>$H4),($P4+1)-$P3,IF(AND($G4=$O4,$O4<$G5,$O4<$O5,$H4<$P3,$P4>$P3),$P4-$P3,IF(AND($G4=$O4,$G4>$G3,$H4>$H3,$P4>$P3,$H4>$P3),$P4-$H4,IF(AND($G4=$O4,$G4<$O5,$P4>$P3,$P4>$H5,$H4>$H3,$H4<$H5),0,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3,$P4>$P5,$P2>$P3),$P4-$P2,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$H4<$H5,$P4<$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$H5,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3),$P4-$P2,IF(AND(ISBLANK($O4),ISBLANK($P4)),0,IF(AND($G4<$O4,$H4<$P3,$P4<$H4),($P4+1)-$P3)))))))))))))))) This seems to work for the most part but there are a few that I just can't get. I also pulled up my query and started to enter in the time durations manually and it couldnt come up with anything automatic for me. There must be an easier way for me to do this other than trying to create an IF formula for each answer that turns up incorrect. I have a screen shot below.
slbloyd17
Dec 18, 2025 Place Excel
135Views
0likes
3Comments
My Percentage of total sales is not 100 - Why?
I have 20 categories of Sales Revenue in Column A, the total revenue for each category is in column B, the % of a category, as it relates to the total categories is in column C but the Total Percentage is over 100% when I total all the percentages, what am I missing? Amount % of Sales Backup & Recover-Desktop Suppor 70,808.50 2.2% Barracuda Back Up - Cloud to Cloud 11,220.00 0.35% Barracuda ESS - Security Edition $51,508.57 1.62% Barracuda Sentinel 20,093.52 0.63% Carrier Management / NOC Support Services $239,618.99 7.54% Cust Support Srvs/Provisioning/vCIO Svcs $98,514.00 3.10% Field Services Support-Single $1,447,941.00 45.56% FTE Tech 159,227.50 5.01% Hardware Lease Revenue 11,114.22 0.35% Help Desk Services 1,604,649.85 50.49% IH-New User_Laptop Setup $230,523.19 7.25% IH-Remote Work Support Services 60,172.85 1.89% MRR - Data Services/Ticket Overage $40,057.00 1.26% Network & Security Hardware $985,825.90 31.02% Network Monitoring Service-Devices 153,696.60 4.84% NRR - Carrier Management / NOC Services 11,245.00 0.35% NRR - ESS Email Security Services 28,865.00 0.91% NRR - Help Desk 46,500.00 1.46% NRR - Migration Implementation 98,595.00 3.10% NRR - Monitoring Services 15,000.00 0.47% Remote Server Monitoring 102,455.03 3.22% Remote Work/Sharepoint Migration $84,026.55 2.64% Shipping & Delivery Fee 49,970.63 1.57% Software/Licenses 491,422.97 15.46% TOTAL $3,178,015.20 192.35%
Nathalie135
Dec 18, 2025 Place Excel
8.1KViews
0likes
8Comments
If calculation returns a negative number, then it needs to be a 0; 2nd part is to show Max number
1. For row 45, I have calculation for 30% of row 42 ex. =F42*.30. If the result is a negative number, then I would like it show a 0 instead of the negative number. 2. Next, for row 48, the result of =F9-F45 can be no greater than F9. So if it is greater than F9, then F9 is the highest number that it should show, in this case $281, but should still show values 0 up to the amount of F9. Thank you
jcopp07
Dec 18, 2025 Place Excel
3KViews
0likes
3Comments
Excel formula help... again!
I have different tabs for each month. I need the total of the last day of month to start in the opening balance on the next tab in the beginning of the month. but some months obviously has 30 days and some 31 and then there's Feb too doing whatever it wants ;-) the total here in AH has to be the last day of the month - here it's the 30th and 379 the opening balance for the next tab (yellow block) has to be whatever the previous month's closing total was. I don't know how to create a formula to choose the last day's total and pull it to the new sheet in the opening balance. Thanks so much
JoJo1
Dec 18, 2025 Place Excel
65Views
0likes
2Comments