HELP- count of duration formula

J

jojo3strada

New Member
Joined
Feb 20, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am spending a long time on this spreadsheet and there is gotta be a better way. I have the delivery date, duration weeks, return date. I need a rule or something that can automatically put a 1 on the weeks date on the right till it hits the end of weeks duration


Delivery DateExpected Duration (Weeks)Expected Return (Date)02/19/2402/26/2403/04/2403/11/2403/18/2403/25/2404/01/2404/08/2404/15/24
2/19/20243711/4/2024111111111
2/19/2024156/3/2024111111111
2/19/2024461/6/2025111111111
2/19/20243711/4/2024111111111
2/19/2024176/17/2024111111111
 

Attachments

  • Screenshot 2024-02-20 143037.png
    Screenshot 2024-02-20 143037.png
    54 KB · Views: 6

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Give this a try. You didn't specify what you want when the Return date is reached, so I used 0.
Book1
ABCDEFGHIJKLMT
1Delivery DateExpected Duration (Weeks)Expected Return (Date)19-Feb-2426-Feb-244-Mar-2411-Mar-2418-Mar-2425-Mar-241-Apr-248-Apr-2415-Apr-243-Jun-24
2
319-Feb-24374-Nov-241111111111
419-Feb-24153-Jun-241111111110
519-Feb-24466-Jan-251111111111
619-Feb-24374-Nov-241111111111
719-Feb-241717-Jun-241111111111
Sheet2
Cell Formulas
RangeFormula
T1T1=S1+7
T3:T7,E3:M7E3=IF(E$1<$C3,1,0)
 
Upvote 0
How does the expected duration come into play? Will it ever be shorter than the expected return date?
 
Upvote 0
You could try this, it is a more complicated formula. But it is just in the first column. No dragging to the right.
Are holidays taken into consideration?

Otherwise the post by @Asbestos_Jen is short a succint.
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1Delivery DateExpected Duration (Weeks)Expected Return (Date)2024-02-192024-02-262024-03-042024-03-112024-03-182024-03-252024-04-012024-04-082024-04-152024-04-222024-04-292024-05-062024-05-132024-05-202024-05-272024-06-032024-06-102024-06-172024-06-242024-07-012024-07-082024-07-152024-07-222024-07-292024-08-052024-08-122024-08-192024-08-262024-09-022024-09-092024-09-162024-09-232024-09-302024-10-072024-10-142024-10-212024-10-282024-11-042024-11-112024-11-182024-11-252024-12-022024-12-092024-12-162024-12-232024-12-3045663
2
32024-02-19372024-11-041111111111111111111111111111111111111
42024-02-19152024-06-03111111111111111
52024-02-19462025-01-061111111111111111111111111111111111111111111111
62024-02-19372024-11-041111111111111111111111111111111111111
72024-02-19172024-06-1711111111111111111
Sheet1
Cell Formulas
RangeFormula
E1E1=DATE(2024,2,19)
F1:AY1F1=WORKDAY.INTL(DATE(2024,2,19),SEQUENCE(1,MAX(B3:B7)),"0111111")
E7:U7,E6:AO6,E3:AX5E3=IF(IFERROR((--(WORKDAY.INTL(DATE(2024,2,19),SEQUENCE(1,MAX(B3:B7)),"0111111")= WORKDAY.INTL(A3,SEQUENCE(1,B3),"0111111"))),0)=1,1,"")
Dynamic array formulas.
 
Last edited:
Upvote 0
Assuming the Delivery Date is in Column A and the Expected Return Date is in Column C
In cell D1 put =SEQUENCE(1,(MAX(C:C)-MIN(A:A))/7,MIN(A:A),7)
In cell D2 put =IF(AND($A2<=D$1,$C2>=D$1)=TRUE,1,0) and drag down and right
 
Upvote 0
dreid1011 said:
How does the expected duration come into play? Will it ever be shorter than the expected return date?
Click to expand...
by week we have a start date when a product goes on rent and a off date when it returns. They made a column to calculate weeks in between and the dates on the left are the weeks they put the #1 for each week between those dates. The form is figuring out stock forecast
1708470024281.png
 
Upvote 0
jojo3strada said:
by week we have a start date when a product goes on rent and a off date when it returns. They made a column to calculate weeks in between and the dates on the left are the weeks they put the #1 for each week between those dates. The form is figuring out stock forecast
View attachment 107187
Click to expand...
Okay. Well, I was going the same route as Jen, so that would be my suggestion. Take your pick though, all the suggested solutions seem to get the job done.

Actually, I added in the delivery date too. Forgot to account for that originally.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Delivery DateExpected Duration (Weeks)Expected Return (Date)8/12/20248/19/20248/26/20249/2/20249/9/20249/16/20249/23/20249/30/202410/7/202410/14/202410/21/202410/28/202411/4/202411/11/202411/18/202411/25/202412/2/202412/9/202412/16/202412/23/202412/30/20241/6/2025
22/19/20243711/4/2024111111111111          
39/2/20249/23/2024   111                
Sheet5
Cell Formulas
RangeFormula
E2:Z3E2=IF(AND(E$1>=$A2,E$1<$C2),1,"")
 
Upvote 0
dreid1011 said:
Okay. Well, I was going the same route as Jen, so that would be my suggestion. Take your pick though, all the suggested solutions seem to get the job done.

Actually, I added in the delivery date too. Forgot to account for that originally.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Delivery DateExpected Duration (Weeks)Expected Return (Date)8/12/20248/19/20248/26/20249/2/20249/9/20249/16/20249/23/20249/30/202410/7/202410/14/202410/21/202410/28/202411/4/202411/11/202411/18/202411/25/202412/2/202412/9/202412/16/202412/23/202412/30/20241/6/2025
22/19/20243711/4/2024111111111111          
39/2/20249/23/2024   111                
Sheet5
Cell Formulas
RangeFormula
E2:Z3E2=IF(AND(E$1>=$A2,E$1<$C2),1,"")
Click to expand...
 
Upvote 0
dreid1011 said:
Okay. Well, I was going the same route as Jen, so that would be my suggestion. Take your pick though, all the suggested solutions seem to get the job done.

Actually, I added in the delivery date too. Forgot to account for that originally.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Delivery DateExpected Duration (Weeks)Expected Return (Date)8/12/20248/19/20248/26/20249/2/20249/9/20249/16/20249/23/20249/30/202410/7/202410/14/202410/21/202410/28/202411/4/202411/11/202411/18/202411/25/202412/2/202412/9/202412/16/202412/23/202412/30/20241/6/2025
22/19/20243711/4/2024111111111111          
39/2/20249/23/2024   111                
Sheet5
Cell Formulas
RangeFormula
E2:Z3E2=IF(AND(E$1>=$A2,E$1<$C2),1,"")
Click to expand...
THANK YOU!!! saved me a bunch of time
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Weekly dates with split across weeks with two months
Replies
1
Views
162
Peter_SSs
Peter_SSs
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
D
  • Solved
Number of Coupons per year.
Replies
2
Views
100
dowtym
D
L
  • Solved
How to get Login/Logout Times for users with multiple login/logouts on the multiple days
Replies
3
Views
192
leenvr
L
J
  • Question
Calculating time frames
Replies
2
Views
109
myall_blues
myall_blues

Forum statistics

Threads
1,215,212
Messages
6,123,653
Members
449,111
Latest member
ghennedy

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