I need to auto-spread project hours into columns by week which are dependent upon a start and finish date AND if today is past the finish date, any remaining hours need to be automatically placed in the current week column. I can't figure out how to combine these functions into one formula.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to spread project hours weekly
- Thread starter LGEARY
- Start date
our company does not allow us to download anything... can i post a picture of what i am asking about?
Upvote
0
awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,563
- Office Version
- 365
- Platform
- Windows
- Web
the problem with pictures is that the forum needs to recreate your scenario completely in order to try to figure out what you want. It helps in that respect. but it is susceptible to typos and errors. And it takes time to do that.
You can copy and paste a range of cells that has your data but be sure the columns you copy have headers so we know what they are (even the column letters would be helpful, and the same concept for rows (although that is not that critical). But that will not copy your formulas so you need to identify the cells with the formulas that you want examined, and the formula itself. Then you should also post a table of expected values.
Usually only 5-10 rows of data is all that is needed, sometimes just one.
We look forward to seeing the data and examples.
Upvote
0
HI... here is what i have so far... Row 2 contains part of the formula in cells K2, M2 and N2.... then i have a different formula in the other rows because i need to somehow combine the 2... ultimate outcome i'm looking for is:
IF column D says it's OPEN, spread the hours by week from start date to issued date BUT if today's date (column N) is =to or passed the Issued date, put the all of the remaining hours in column N.
| DesignerDB - for help.xlsm | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | Designer Name | Task # | Task | Task Status | Estimated Hours | Spent Hours | Remaining Hours | Start Date | Actual Finish Date | To be Issued By Date | Rem # of Hours per week | # OF WEEKS REMAINING | 04/23/23 | 04/30/23 | 05/07/23 | 05/14/23 | 05/21/23 | 05/28/23 | 06/04/23 | |||
| 2 | Allen, Nathan | 1 | IFC | Closed | 6.0 | 2.0 | 4.0 | 11/22/21 | 2/7/23 | 2/10/23 | 4.0 | 0.0 | 4.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |||
| 3 | Allen, Nathan | 60 | OTHER | Closed | 80.0 | 0.0 | 80.0 | 5/15/23 | 6/12/23 | 6/30/23 | 7 | 7 | 11.4 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 4 | Allen, Nathan | 13 | IFR | Open | 30.0 | 0.0 | 30.0 | 3/6/23 | 3/17/23 | 4/30/23 | 8 | 8 | 3.8 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 5 | Allen, Nathan | 13 | IFC | Open | 10.0 | 0.0 | 10.0 | 3/20/23 | 3/31/23 | 3/31/23 | 2 | 2 | 5.0 | 10.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 6 | Allen, Nathan | 14 | IFR | Open | 20.0 | 0.0 | 20.0 | 3/6/23 | 3/10/23 | 6/15/23 | 14 | 15 | 1.3 | 1.3 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 7 | Allen, Nathan | 14 | IFC | Open | 4.0 | 0.0 | 4.0 | 3/13/23 | 3/17/23 | 3/17/23 | 1 | 1 | 4.0 | 4.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 8 | Allen, Nathan | 100 | OTHER | Open | 10.0 | 0.0 | 10.0 | 3/20/23 | 3/20/23 | 3/24/23 | 1 | 1 | 10.0 | 10.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 9 | Allen, Nathan | 5 | AS-BUILT | As-Built In Process | 10.0 | 0.0 | 10.0 | 2/13/23 | 2/24/23 | 3/9/23 | 3 | 4 | 2.5 | 10.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 10 | Allen, Nathan | 2 | AS-BUILT | As-Built In Process | 40.0 | 0.0 | 40.0 | 2/20/23 | 3/3/23 | 3/30/23 | 5 | 6 | 6.7 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 11 | Allen, Nathan | 120 | AS-BUILT | As-Built In Process | 20.0 | 5.0 | 15.0 | 1/9/23 | 1/16/23 | 1/30/23 | 3 | 3 | 5.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 12 | Allen, Nathan | 168 | AS-BUILT | As-Built In Process | 3.0 | 2.0 | 1.0 | 1/30/23 | 2/14/23 | 2/28/23 | 4 | 5 | 0.2 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| 13 | Allen, Nathan | 169 | AS-BUILT | As-Built In Process | 10.0 | 0.0 | 10.0 | 3/13/23 | 3/17/23 | 3/31/23 | 3 | 3 | 3.3 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
Allen, Nathan | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| N1 | N1 | =(TODAY())+8-WEEKDAY(TODAY()) |
| O1:T1 | O1 | =N1+7 |
| K2 | K2 | =IFERROR(($G2/$M2),$G2) |
| M2 | M2 | =IF(ROUND(($J2-$N$1)/7,0)>=0,ROUND(($J2-$N$1)/7,0),0) |
| N2 | N2 | =IF($N$1>=$J2,$G2,$K2) |
| O2:T13 | O2 | =IF(E2="open",(IF(O$1=MEDIAN($H2,$J2,O$1),$M2,IF($J2<TODAY(),$G2,"0.0"))),"0.0") |
| K3:K13 | K3 | =(J3-H3)/7 |
| L3:L13 | L3 | =ROUNDUP(K3,0) |
| M3:M13 | M3 | =+G3/L3 |
| N3:N13 | N3 | =IF($D2="open",(IF(N$1=MEDIAN($H3,$J3,N$1),$M3,IF($J3<TODAY(),$G3,"0.0"))),"0.0") |
| G2:G13 | G2 | =+E2-F2 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A2:A13 | List | ='Dropdown lists'!$B$3:$B$16 |
| C2:C20 | List | ='Dropdown lists'!$L$3:$L$11 |
| D2:D20 | List | ='Dropdown lists'!$N$3:$N$6 |
Upvote
0
i have figured this out so no longer need help.... glad to know this is here though.... have a good day.
Upvote
0
Similar threads
- Question
- Solved
