Rounding date calculation to nearest Friday or Monday

[Steve]

I created a simple module that based on a known starting
date ("=now()") and the delivery lead-time in weeks, it
will give you a delivery date. But I need that final
delivery date to round to the nearest Friday or Monday.
Let's suppose that the "=now()" in in cell C9 and in cell
D9 you enter the lead-time in weeks, i.e., 1, 2, 3..., in
cell E9 is where the "=now()" plus number of weeks
returns a pecific date. If that date is, let's say
November 16, 2003 (Sunday), I want it to roll to November
17, 2003 (Monday). The same thing would be needed when
cell E9 returns a Saturday (November 15, 2003). I want it
to return the prior Firday (November 14, 2003). The exact
calculation I currently have installed in cell E9 is "=SUM
((C9+(D9*7))+3)"

Thanks for any help!!

 

[Peo Sjoblom]

Maybe something like this

=IF(WEEKDAY(((C9+(D9*7))+3),2)=7,((C9+(D9*7))+3)+1,IF(WEEKDAY(((C9+(D9*7))+3
),2)=6,F9-1,((C9+(D9*7))+3)))

 

[Steve]

That did it, Peo! I thank you very much for your
assistance!!

Steve

 

[J.E. McGimpsey]

Peo -

What does F9 contain in your formula? I get a negative date for C9 =
11/05/2003.

 

[BrianB]

I guess that this is what you want, which changes Saturday to
Friday,Sunday to Monday, otherwise leaving the same weekday.

=IF(WEEKDAY(C6)=1,C6 +(D6*7)+1,IF(WEEKDAY(C6)=7,C6 +(D6*7)-1,C6
+(D6*7)))


Regards
BrianB
==========================================================

 

[Ron Rosenfeld]

I created a simple module that based on a known starting
date ("=now()") and the delivery lead-time in weeks, it
will give you a delivery date. But I need that final
delivery date to round to the nearest Friday or Monday.
Let's suppose that the "=now()" in in cell C9 and in cell
D9 you enter the lead-time in weeks, i.e., 1, 2, 3..., in
cell E9 is where the "=now()" plus number of weeks
returns a pecific date. If that date is, let's say
November 16, 2003 (Sunday), I want it to roll to November
17, 2003 (Monday). The same thing would be needed when
cell E9 returns a Saturday (November 15, 2003). I want it
to return the prior Firday (November 14, 2003). The exact
calculation I currently have installed in cell E9 is "=SUM
((C9+(D9*7))+3)"

Thanks for any help!!

I'm not sure whether you want a Wednesday to round to the preceding Monday or
the following Friday, but this formula is easily modified:

=A1+CHOOSE(WEEKDAY(A1),1,0,-1,-2,1,0,-1)


--ron

 

[Peo Sjoblom]

Oops! I made the formula first with a date in F9, then I replaced that cell
with the OPs
formula everywhere but for this one> So am a bit surprised that it worked
for the OP ?
It should have been replaced with ((C9+(D9*7))+3)
These things happen to me all the time <g>

 

[J.E. McGimpsey]

<forehead slap>Of course!</forehead slap>

<AOL>Me too!</AOL>

<g></g>