Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the IF function to calculate date | Excel Discussion (Misc queries) | |||
Calculate age as of a given date | Excel Discussion (Misc queries) | |||
Calculate age as of a date certain | Excel Discussion (Misc queries) | |||
Calculate date of birth in Excel | Excel Worksheet Functions | |||
Inserting Blank Rows Macro? | Excel Worksheet Functions |