calculate weeks from a start date ( not yr weeks)

[Todd F.]

I have a tool where a beginning date is entered and then I need toi calulate
number of weeks between my beginning date and whatever date we are at for the
next 20 weeks.

I need to round to whole number for the week. I am not interested in the
excel calulated week of the yr - I need to do weeks (time ) between the user
specified start date and anygiven date up to the end of the 20 week project.

I have been search excel pages all day but not seeing anything - thanks for
the help.

Todd Frisch (e-mail address removed)

 

[Sandy Mann]

Todd,

I may be misunderstanding your requirements but wigth your start date in A1
would

=MIN(20,INT((TODAY()-A1)/7)+1)

give you what you want?

Regards

Sandy

 

[Todd F.]

Thanks for reply but I may not have explained well enough - see below where I
need the week column to tell me what wk I am in so if I satrt on friday the
26th I then need to know when the week changes - My time runs about 20 weeks

I could start on any day or date - the person punches in the fill date and
my if statements build my time line for them.

Your formula did not work - thanks for the time.

Fill Date 11/26/04
Date CVFF WK Day week
11/26/04 48 Friday 1
11/27/04 48 Saturday 1
11/28/04 49 Sunday 1
11/29/04 49 Monday 1
11/30/04 49 Tuesday 1
12/01/04 49 Wednesday 1
12/02/04 49 Thursday 1
12/03/04 49 Friday 2
12/04/04 49 Saturday 2
12/05/04 50 Sunday 2
12/06/04 50 Monday 2
12/07/04 50 Tuesday 2
12/08/04 50 Wednesday 2
12/09/04 50 Thursday 2
12/10/04 50 Friday 3
12/11/04 50 Saturday 3
12/12/04 51 Sunday 3
12/13/04 51 Monday 3
12/14/04 51 Tuesday 3
12/15/04 51 Wednesday 3
12/16/04 51 Thursday 3

 

[Sandy Mann]

Assuming that

11/26/04 48 Friday

is in A3:C3 then

=INT((A3-$A$3)/7)+1

entered in D3 and copied down gives me the week numbers you want.

Incidentally I got the names of the days of the week with the formula

=TEXT(A3,"dddd")

HTH

Sandy

 

[Todd F.]

Hey thanks allot I will use the main formula plus I will use the day formula
and do away with my lookup tables which was a little bulky.

I appreciate the help I was screweing around with less efficient if
statements but this is much better way

 

[Sandy Mann]

Your're welcome, thanks for the feedback.

If your dates are consecutive then you actually do not need the day name
formula at all. Just enter the starting day name in the first cell then
drag it down using the fill handle and Excel will fill in all the day names
without needing any formula.

Good luck

Sandy

 

[Todd F.]

thanks
I will try it.

Your're welcome, thanks for the feedback.

If your dates are consecutive then you actually do not need the day name
formula at all. Just enter the starting day name in the first cell then
drag it down using the fill handle and Excel will fill in all the day names
without needing any formula.

Good luck

Sandy