•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Help on tracking days
Our take
Tracking project timelines in spreadsheets can seem daunting, especially when managing phases with breaks in between. You’ve already made progress by using formulas to calculate days while excluding weekends and holidays. The challenge arises when consolidating these periods in a pivot table, as it may not accurately reflect the total days taken for phases that are split across different intervals. Fortunately, there are ways to adjust your pivot table settings to showcase the total days while still calculating averages effectively.
Hello,
I am pretty new to spreadsheets and I hit a wall and google is not helping. Signed up to reddit just to ask this. Not sure how to explain this easily so there will be examples of what’s going on.
For the multiple projects I am tracking days taken using the formula the subtracts weekend and holidays for each phase
Project name. Phase. StartDate EndDate DaysTaken
————————————————————————————————————
Project 1 Phase A 10/31/25 11/11/25 8
Project 1 Phase B 11/12/25 12/4/25 15
Project 2 Phase A 10/31/25 11/11/25 8
Project 2 Phase B 11/12/25 12/4/25 15
I am putting this into a pivot table and averaging days taken for each phase from all the projects
Project name. PhaseA. Phase B
—————————————————
Project 1 8 15
Project 2 8 15
—————————————————
Average 8 15
Now I have a project that phase had to pause and wait for something to finish. So the data looks like this
Project name. Phase. StartDate EndDate DaysTaken
————————————————————————————————————
Project 3 Phase A 3/6/26 3/11/26 4
Project 3 Phase A 3/16/26 3/20/26. 5
So the pivot table is only taking the 4 not the 9
Project name. PhaseA. Phase B
—————————————————
Project 1 8 15
Project 2 8 15
Project 3 4
—————————————————
Average 7 15
I know if I change the plot table Values to Sum it will count the days as 9 but it will just add all the phase days together at the bottom. Is there a way to get the 9 to show up, but still get the total average at the bottom of the column?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Creating Pivot Table from Multiple SheetsHi All, I'm working on a large tracking workbook, consisting of several clinical trials in order to track by patient detail the payments we are owed by the funder, what we have received, and the difference. All these payments are delayed by 3m-2 years in some cases and leadership wants to accurately predict how much we are owed. I think what where I'm running into issues is that while I did standardized as much as I could, there are still several columns for each study that don't apply to other studies. I.e. some studies have different arms they could be enrolled in, some are just a 1 time enrollment payment, others have several milestones that can receive payments. But every sheet has roll ups that are standardized that I need in the Pivot Table. Those being: Protocol Randomized Date Federal Accrued Foundation Accrued Industry Accrued Supplement Accrued Federal Received Foundation Received Industry Received Supplement Received Total Owed The Accrued and received columns sum the individual payments into those buckets, that way we can go back to the funder and ask specifically what we are missing for to see if they missed paying us for that milestone specifically. When I tried pulling all these sheets into Power Query, I was able too, and aggregated all the sheets into one via Power Query. Then I tried to pull that aggregate into a pivot table. No Pivot table Loaded and all I got was "load to data model failed" on each queries. Am I asking for too much? Can I get rid of the extra columns in Power Query that do no align together with ruining the data that is being pulled in by formulas. I have if statements pulling into the table for the individual, study specific milestones, from a separate table that automatically helps us track payments accrued, and the "standard columns" have sums formulas that sum the columns that apply to them from the individual milestone columns. The milestone, study specific received columns are entered in manually and have no formulas, but are rolled up into the standard columns just like in the accrued side. And the total owed column is also a formula of the standard accrued and received columns. The goal of pulling this into a pivot table is so we can give high level data to leadership to actually start tracking how much we are owed, given the constant delay in payments, and to have a real sense of the deficit this specific program runs year to year. This way they can accurately plan for the yearly "donation" from other sources of funding in the department. If you made it through this post, thank you! Any help is appreciated. I'm using Excel 365. submitted by /u/Melodic-Pollution-91 [link] [comments]
- Trouble using pivot tables to calculate field and subfield values.I am having trouble getting a clean summary of our monthly stats for a surgical practice I work for. I am using a pivot table connected to the surgical case spreadsheet. In the spreadsheet, each row represents an individual case. I am struggling to get these values in the same field: 1) Monthly total of days the practice performed surgery 2) Subfields showing Monthly total of days each doctor performed surgery The issue is that I cannot figure out how to calculate both of these in the same field. "Sum of Days Worked" returns the correct monthly total, but the wrong total of days for that individual doctor. This is because a "1" gets placed in this column next to the first surgery of the day. So, that value only gets assigned to the doctor who performed the first surgery of the day. "Sum of Dr Day" returns a "1" on the first surgery each doctor performed that day. This calculates the correct total of days each doctor performed surgery, but the monthly total is off, because if there are more than one scheduled per day, it counts the day twice. I understand I can achieve the desired totals with regular formulas, but we are constantly having new doctors join and leave our practice, so I prefer pivot tables because I don't have to continuously adjust formulas. Google tells me "Add this Data to the Data Model" is helpful for this problem, but we only use Macbooks at work. Any help is appreciated- this is driving me crazy. submitted by /u/anarchybats [link] [comments]
- Index Match or Xlookup to return sum of row values based on matching two horizontal lookups?Ask: If E59:S59=E111 AND E60:s:60<-E112, give me the sum of E61:71,else return 0 What is the smoothest formula for this? I added in what I'm trying to do on the formula line. ChatGPT is less helpful with this than I am. I want to bundle by phase because our timesheet system doesn't allow me to pull a report by phase/task, only by person. I tried Xlookup but kept getting SPILL errors so I am at a loss. FTE1 is a different category and should not be included (PM, I can plug that as it's always in the same position). Thanks in advance for your help. https://preview.redd.it/yp4yzfn2rcxg1.png?width=2560&format=png&auto=webp&s=86eed1d2c8f3bb97c6d49592a0b1978d5f80f365 submitted by /u/Regular_or_BQ [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#big data management in spreadsheets#rows.com#AI formula generation techniques#AI-native spreadsheets#conversational data analysis#Excel compatibility#google sheets#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#predictive analytics in spreadsheets#cloud-native spreadsheets#data visualization tools#formula generator#enterprise data management#big data performance#Excel alternatives