Sum of Hours w.r.t Today's Date

S

shashik

New Member
Joined
Mar 7, 2017
Messages
17
So Friends,

I need help regarding below table.

I needs "Hours" for respective Resource, basis the current day.

Please note the range of dates extends beyond below table.

I tried doing it by IF function, but it is too complicated for me after 1st/2nd Logic for me.


[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD="align: center"]27-Nov-17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]04-Nov-17[/TD]
[TD="align: center"]11-Nov-17[/TD]
[TD="align: center"]18-Nov-17[/TD]
[TD="align: center"]25-Nov-17[/TD]
[TD="align: center"]02-Dec-17[/TD]
[TD="align: center"]09-Dec-17[/TD]
[TD="align: center"]16-Dec-17[/TD]
[TD="align: center"]23-Dec-17[/TD]
[TD="align: center"]30-Dec-17[/TD]
[/TR]
[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"]10-Nov-17[/TD]
[TD="align: center"]17-Nov-17[/TD]
[TD="align: center"]24-Nov-17[/TD]
[TD="align: center"]01-Dec-17[/TD]
[TD="align: center"]08-Dec-17[/TD]
[TD="align: center"]15-Dec-17[/TD]
[TD="align: center"]22-Dec-17[/TD]
[TD="align: center"]29-Dec-17[/TD]
[TD="align: center"]05-Jan-18[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Please Note I need the Sum of Hours including the future basis the current date.

shashik said:
So Friends,

I need help regarding below table.

I needs "Hours" for respective Resource, basis the current day.

Please note the range of dates extends beyond below table.

I tried doing it by IF function, but it is too complicated for me after 1st/2nd Logic for me.


[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD="align: center"]27-Nov-17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]04-Nov-17[/TD]
[TD="align: center"]11-Nov-17[/TD]
[TD="align: center"]18-Nov-17[/TD]
[TD="align: center"]25-Nov-17[/TD]
[TD="align: center"]02-Dec-17[/TD]
[TD="align: center"]09-Dec-17[/TD]
[TD="align: center"]16-Dec-17[/TD]
[TD="align: center"]23-Dec-17[/TD]
[TD="align: center"]30-Dec-17[/TD]
[/TR]
[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"]10-Nov-17[/TD]
[TD="align: center"]17-Nov-17[/TD]
[TD="align: center"]24-Nov-17[/TD]
[TD="align: center"]01-Dec-17[/TD]
[TD="align: center"]08-Dec-17[/TD]
[TD="align: center"]15-Dec-17[/TD]
[TD="align: center"]22-Dec-17[/TD]
[TD="align: center"]29-Dec-17[/TD]
[TD="align: center"]05-Jan-18[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]Resource 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
steve the fish said:
What does the below mean?
Click to expand...

So Today is 27-11. So the Total no of hours for Resource 1 should be sum of all columns from week 25-Nov to 1-Dec-17 to the last week i.e. 30-Dec to 05-Jan.

So in this case it would be 120 Hours.

Consider this as a calculation of sum of hours the Resource is allocated each from today.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
152
Tetra201
Tetra201
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
229
Fluff
Fluff
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
S
  • Solved
Calculate Backdated dates
Replies
5
Views
290
Joe4
Joe4
S
  • Question Question
Shifting cell reference
Replies
5
Views
113
Cubist
Cubist

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top