Project Hours - Weekly Reporting

mystique07

New Member
Joined
Mar 30, 2018
Messages
12
We have multiple projects and I need to report the weekly consumed hours each week.

i have used SUMIFS to get the monthly totals. but don't know how to get the consumed hours as of each week which should be total hours - consumed till date.


Book1
ABCDEFGHI
1NameProjectJunJulAug4-Sep11-Sep18-Sep25-Sep
2Name 1ABC1005010040404040
3Name 2ABC1005010020202020
4Name 2MNC1005010020202020
Sheet1


 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
What do you want your end result to look like?
 

mystique07

New Member
Joined
Mar 30, 2018
Messages
12
Hi,

I need something like the below so that I can report it at the end of the week.

Help me only with calculating "Consumed - As of 9/4"

Total HoursConsumed - As of 9/4Available
Project Name - ABC500300200
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Ok. Where are the Total Hours coming from? They do not appear in your first table.
 

mystique07

New Member
Joined
Mar 30, 2018
Messages
12

ADVERTISEMENT

Look at Column F - Sep. That's how I calculate the monthly totals.

Book1
ABCDEFGHIJ
1NameProjectJunJulAugSep4-Sep11-Sep18-Sep25-Sep
2Name 1ABC1005010016040404040
3Name 2ABC100501008020202020
4Name 2MNC100501008020202020
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=SUMIFS(Table1[@[4-Sep]:[25-Sep]],Table1[[#Headers],[4-Sep]:[25-Sep]],"*Sep*")
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I think you missed understanding my question. In post #3, you have a column indicating Total Hours. This is not to be found in your original post. I assume this is your budget for the project. Is that correct. If so, where does it come from so that it can be incorporated into determining the Available Hours remaining?
 

mystique07

New Member
Joined
Mar 30, 2018
Messages
12

ADVERTISEMENT

That total hour is determined before a project start using various factors. I enter that manually to calculate the remaining hours
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you wish to have it as part of the solution you are looking for, you need to provide it to us. How else can we give you the expected solution you have shown
 

mystique07

New Member
Joined
Mar 30, 2018
Messages
12
Can you help me with the below. the totals are calculated from the monthly hours

Book1
ABCDEFGHIJK
1NameProjectTotalJunJulAugSep4-Sep11-Sep18-Sep25-Sep
2Name 1ABC4101005010016040404040
3Name 2ABC330100501008020202020
4Name 2MNC330100501008020202020
Sheet1
Cell Formulas
RangeFormula
C2:C4C2=SUM(Table1[@[Jun]:[Sep]])
G2:G4G2=SUMIFS(Table1[@[4-Sep]:[25-Sep]],Table1[[#Headers],[4-Sep]:[25-Sep]],"*Sep*")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,736
Members
414,170
Latest member
Mdm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top