Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Todd F.
 
Posts: n/a
Default calculate weeks from a start date ( not yr weeks)

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
  #2   Report Post  
Sandy Mann
 
Posts: n/a
Default

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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



  #3   Report Post  
Todd F.
 
Posts: n/a
Default

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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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




  #4   Report Post  
Sandy Mann
 
Posts: n/a
Default

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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






  #5   Report Post  
Todd F.
 
Posts: n/a
Default

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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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








  #6   Report Post  
Sandy Mann
 
Posts: n/a
Default

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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










  #7   Report Post  
Todd F.
 
Posts: n/a
Default

thanks
I will try it.



"Sandy Mann" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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" wrote:

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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Todd F." wrote in message
...
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











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I use the IF function to calculate date Pulling My Hair Out! Excel Discussion (Misc queries) 1 December 11th 04 12:03 AM
Calculate age as of a given date Joe S. Excel Discussion (Misc queries) 1 December 9th 04 10:02 PM
Calculate age as of a date certain Joe S. Excel Discussion (Misc queries) 1 December 9th 04 10:02 PM
Calculate date of birth in Excel jamaicansuga Excel Worksheet Functions 2 November 11th 04 04:34 PM
Inserting Blank Rows Macro? Michael Saffer Excel Worksheet Functions 2 November 9th 04 07:23 PM


All times are GMT +1. The time now is 11:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"