Forum Widgets
Latest Discussions
Need assistance to correct a formula
I am using the following formula to calculate weekly hours. I want to change it to calculate the hours with the starting on Monday going to Sunday and display the result in column G on the Sunday. For example - calculate totals from Monday Jan 6 to Sunday Jan 12, inclusive. Thanks in advance for your help. =IF(WEEKDAY(B6)=7, IF(SUMIFS(D:D,B:B, ">="&B6-6,B:B, "<="&B6)>0, SUMIFS(D:D,B:B, ">="&B6-6,B:B, "<="&B6), ""), "") A B C D E F G 1 Date Hours Purchases Rate Daily Cost Hrs / wk 2 1-Jan Wed 5 $20.00 $100.00 3 2-Jan Thu 5 $0.00 4 3-Jan Fri $20.00 5 4-Jan Sat $20.00 10.00 6 5-Jan Sun 7 $20.00 $140.00 7 6-Jan Mon $20.00 8 7-Jan Tue $20.00 9 8-Jan Wed $20.00 10 9-Jan Thu $20.00 11 10-Jan Fri $20.00 12 11-Jan Sat $20.00 7.00 13 12-Jan Sun 3 $20.00 $60.00
Move repeating columns into rows
Hello guys, I have a set of data that looks like this: Name Hours Date Hours Date Hours Date John 3 1-Jan 4 5-Jan Ann 4 4-Jan 2 8-Jan 2 9-Jan Each Hours data cell have a comment in it, and I'm trying to turn it into something like this: Name Hours Date John 3 1-Jan John 4 5-Jan Ann 4 4-Jan Ann 2 8-Jan Ann 2 9-Jan Is there a way for me to do that while retaining all the comments in each Hours data cell? I'm using Excel 2016. Best Regards, John
Calculate hours using pivot table
Hi, Im making a personell planning sheet and I want to calculute the hours each teacher is teaching using a pivot table. My data is formatted in 2 tables like this (simplified): Lesson Hours Teacher 1 Teacher 2 Lesson 1 2 Paul Lesson 2 3 Pete Lesson 3 2 Paul Pete Teacher Max working hours Paul 10 Pete 15 Now its easy to create an overview of how many hours each teacher is teaching with one teacher collumn. But it need to calculate the hours based on 2 collumns like this: Paul -> Lesson 1 + Lesson 3 = 4 hours Pete -> Lesson 2 + Lesson 3 = 5 hours Then the next step is to use a metric or KPI to calculate if the teacher is exceeding their max working hours. I hope someone can help me out with this... Thanks!Playing with conditional formatting
This formatting is something I used to restructure the way I wrote formulas between 2015 and 2018. In the original I used VBA but now, as often happens, worksheet formulas used within conditional formatting are sufficient. The formula defining the condition is = DIRECTREFλ(A1) //Function to test for the presence of direct cell references DIRECTREFλ = LAMBDA(cell, IF(ISFORMULA(cell), REGEXTEST(FORMULATEXT(cell), "\b[A-Z]{1,3}\${0,1}\d{1,6}\b")) ); The original was pretty effective as a training aid to avoid the practice of relative referencing and use defined names! That said, not many followed my lead! Mind you when I posted a discussion on Chandoo it didn't meet with overwhelming support, though subsequent events in the form of spilt ranges have made my life so much easier! The post did however stimulate more discussion than I had anticipated. The A1 notation is an abomination that has no place within serious spreadsheet design -- discuss? | Chandoo.org Excel Forums - Become Awesome in ExcelHelp with changing a formula
Hi - I am using a formula to sum hours worked for a week. Currently it calculating the values beginning on Sunday to Saturday. I would like to change it to sum from Monday to Sunday and display the result in column G on the Sunday of that week. I'm hoping you can help me. TIA I am currently using the following formula. =IF(WEEKDAY(A2)=7, IF(SUMIFS(C:C,A:A, ">="&A2-6,A:A, "<="&A2)>0, SUMIFS(C:C,A:A, ">="&A2-6,A:A, "<="&A2), ""), "") A B C D E F G H 1 Date Hours Purchases Rate Daily Cost Hrs / wk 2 1-Jan Wed 2.5 $20.00 $50.00 3 2-Jan Thu 4 3-Jan Fri $20.00 5 4-Jan Sat 3 $20.00 $60.00 5.50 6 5-Jan Sun $20.00 7 6-Jan Mon $20.00 8 7-Jan Tue $20.00 9 8-Jan Wed $20.00 10 9-Jan Thu $20.00 11 10-Jan Fri $20.00 12 11-Jan Sat 3 $20.00 $60.00 3.00 13 12-Jan Sun 3 $20.00 $60.00
Not Allowing Entries to be blank
I have a cell that is formatted for a date that will get updated by the user (F4 Below). And then column A is just copying that value all the way down. I want to prevent the information in column A from being deleted accidentally. It will likely be a hidden column, so accidentally deleting it is possible. I tried data validation and rules, and they worked in all cases except if they are deleted. How can I prevent A1 from being deleted, but still have the user be able to update the date in F4 and have A1 update.Calculating hours using pivot table
Hi, I'm making a personell planning sheet and I want to calculate the sum of hours teachers give lessons. I have 2 tables and my data is formatted like this (simplified) Lesson name Hours Teacher 1 Teacher 2 Lesson 1 2 Paul Lesson 2 3 Pete Lesson 3 2 Paul Pete Teacher name Max working hours Paul 10 Pete 15 Now I want to create an overview of how many hours each teacher is teaching using a pivot table. This is easy when there is just one Teachter collumn but I need to calculate the sum using both teacher collumns... I need to overview to be something like this: Paul -> Lesson 1 + Lesson 3 = 4 hours Pete -> Lesson 2 + lessen 3 = 5 hours Then the next step is to use a metric or KPI to calculate if each teacher is exceeding their max working hours... Can anybody hlelp me with these problems? Thanks!
Gantt Chart Weekday Function
I am trying to use a gantt chart with conditional formatting for a project. I have my conditional formatting set up the following: =AND($D9<=H$5, WORKDAY($D9-1, $C9)>=H$5) problem is, some of the tasks have a duration of less than 1 day (column C) and so adding the -1 is blanking out several tasks. How do i correct this? I'm just starting to learn conditional formatting so some of this is still like a foreign language to me. Thanks!
