Need formula

Ron99

Active Member
Joined
Feb 10, 2010
Messages
347
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Below is the sample data

2-Sep-249-Sep-2416-Sep-24
SNO.START DATEEND DATETATMANHRSWeek 36Week 37Week 38
13-Sep-244-Sep-2419???
23-Sep-248-Sep-24317??????
33-Sep-2416-Sep-24210?????????

From the above table. There is start date and end date. The output I require is the formula should calculate which week does the start and end date fall and divide the manhours below should be the output.

2-Sep-249-Sep-2416-Sep-24
SNO.START DATEEND DATETATMANHRSWeek 36Week 37Week 38
13-Sep-244-Sep-24199
23-Sep-248-Sep-243178.58.5
33-Sep-2416-Sep-242103.333.333.33

I have a huge data the data goes further down and across. Please help.

Thanks,
Ron
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hmmm row 2 should be only for week 1, I guess.
 
Upvote 0
Yes, you are right. My bad. Let's consider the row 2 end date as 9-sep-24. Any help on the formula ?
 
Upvote 0
My apologies, I have not taken into account year-to-year transition...
 
Last edited:
Upvote 0
Second attempt:

Excel Formula:
=IF(MAX(MIN(F$1+7,$C3)-MAX(F$1,$B3)+1,0)>0,$E3/((($C3-MOD($C3-2,7)-($B3-MOD($B3-2,7)))/7)+1),"")
 
Upvote 0
Solution
In F3 then copied to full range.
Excel Formula:
=IF((F$1<=$C3)*((F$1+6)>=$B3),$E3/(MATCH($C3,$F$1:$J$1,1)-MATCH($B3,$F$1:$J$1,1)+1),"")
1726149570156.png
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,225,302
Messages
6,184,158
Members
453,220
Latest member
flyingdutchman_

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