I work with a schedule that is based on weeks in a year, for example, 0605.
This would be the sixth week of 2005. I need to schedule backwards based on
an end date 0406.
I know the end date, 4505 or 0406 (may be in cell D1 and D2).
I know the duration, for example 10 weeks and 12 weeks (may be in cell C1
and C2).
What I am looking for help is a formula for how to find a start date.

Cell D1 = 4505
Cell C1 = 10
Cell B1 = 3505 (what is the formula to get this).

Cell D2 = 0406
Cell C2 = 12
Cell B2 = 4405 (what is the formula to get this).

I deal with about 100 to 150 of these in each spreadsheet.

I am using MS Excel 97

Hi

a couple of questions - how are you entering the value in D2 - when it
0406 - is is text or a custom number format? (to test type =isnumber(D2) in
E2 and let me know if it returns True or False).
Additionally, will the duration ever exceed 52 weeks.

Cheers
JulieD

"appeng" wrote in message
...
I work with a schedule that is based on weeks in a year, for example, 0605.
This would be the sixth week of 2005. I need to schedule backwards based
on
an end date 0406.
I know the end date, 4505 or 0406 (may be in cell D1 and D2).
I know the duration, for example 10 weeks and 12 weeks (may be in cell C1
and C2).
What I am looking for help is a formula for how to find a start date.

Cell D1 = 4505
Cell C1 = 10
Cell B1 = 3505 (what is the formula to get this).

Cell D2 = 0406
Cell C2 = 12
Cell B2 = 4405 (what is the formula to get this).

I deal with about 100 to 150 of these in each spreadsheet.

I am using MS Excel 97

Julie,

Using =isnumber(D2) returns False.
In 2004 the duration was 53 weeks.

Hope that helps you to help me.
Thank You



"JulieD" wrote:

Hi

a couple of questions - how are you entering the value in D2 - when it
0406 - is is text or a custom number format? (to test type =isnumber(D2) in
E2 and let me know if it returns True or False).
Additionally, will the duration ever exceed 52 weeks.

Cheers
JulieD

"appeng" wrote in message
...
I work with a schedule that is based on weeks in a year, for example, 0605.
This would be the sixth week of 2005. I need to schedule backwards based
on
an end date 0406.
I know the end date, 4505 or 0406 (may be in cell D1 and D2).
I know the duration, for example 10 weeks and 12 weeks (may be in cell C1
and C2).
What I am looking for help is a formula for how to find a start date.

Cell D1 = 4505
Cell C1 = 10
Cell B1 = 3505 (what is the formula to get this).

Cell D2 = 0406
Cell C2 = 12
Cell B2 = 4405 (what is the formula to get this).

I deal with about 100 to 150 of these in each spreadsheet.

I am using MS Excel 97

On Sun, 6 Feb 2005 06:45:02 -0800, appeng
wrote:

I work with a schedule that is based on weeks in a year, for example, 0605.
This would be the sixth week of 2005. I need to schedule backwards based on
an end date 0406.
I know the end date, 4505 or 0406 (may be in cell D1 and D2).
I know the duration, for example 10 weeks and 12 weeks (may be in cell C1
and C2).
What I am looking for help is a formula for how to find a start date.

Cell D1 = 4505
Cell C1 = 10
Cell B1 = 3505 (what is the formula to get this).

Cell D2 = 0406
Cell C2 = 12
Cell B2 = 4405 (what is the formula to get this).

I deal with about 100 to 150 of these in each spreadsheet.

I am using MS Excel 97


The solution depends, in part, on how you are defining the week number. Some
years can have 53 weeks. If you are
using the standard ISO definition
the year will always be in this century
then week/year entries are entered as numbers
then the following VBA routine should work.

To enter this routine, Tools/Macros/Visual Basic Editor should open the VB
Editor. Ensure your project is highlighted in the Project Explorer window,
then Insert/Module and paste the code below into the window that opens.

To use this function, enter the function

=WkSubtr(dt, NumWeeks)

into some cell where dt refers to your wk/yr construct representing the date,
and NumWeeks refers to the number of weeks you wish to subtract. These can be
either values or refer to cells containing the appropriate values.

As written, the routine does NOT do error checking for incompatible values.

If this helps, it can be easily modified to do error checking, and also to be
sensitive to dates outside of the 21st century.

=================================================
Option Explicit

Function WkSubtr(dt, NumWeeks) As Integer
Dim Dt1 As Date
Dim WknumFirstMonday As Integer
Dim Yr As Integer
Dim WkNum As Integer

Yr = 2000 + dt Mod 100
WkNum = Int(dt / 100)

'First Monday of year
Dt1 = DateSerial(Yr, 1, 1) + 7 - Weekday(DateSerial(Yr, 1, 1) + 5)

'ISOWeeknumber of First Monday
WknumFirstMonday = ISOWeeknum(Dt1)

'Adjust wknum for 53 week years
If WknumFirstMonday = 2 Then WkNum = WkNum - 1

'compute starting date
Dt1 = Dt1 + 7 * (WkNum - 1)

'subtract number of weeks
Dt1 = Dt1 - 7 * NumWeeks

'compute new weeknum/year
Yr = Year(Dt1) - 2000
WkNum = ISOWeeknum(Dt1)

WkSubtr = WkNum * 100 + Yr
End Function

Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
=======================================
--ron

On Sun, 06 Feb 2005 12:29:51 -0500, Ron Rosenfeld
wrote:

The solution depends, in part, on how you are defining the week number. Some
years can have 53 weeks. If you are
using the standard ISO definition
the year will always be in this century
then week/year entries are entered as numbers
then the following VBA routine should work.

Actually, the week/yr entries (i.e. 0406) can be entered as text, also. VB
will coerce it into a number.


--ron

It might pay to read what Chip Pearson has on his web site about week
numbers:


http://www.cpearson.com/excel/weeknum.htm


--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"appeng" wrote in message
...
I work with a schedule that is based on weeks in a year, for example, 0605.
This would be the sixth week of 2005. I need to schedule backwards based on
an end date 0406.
I know the end date, 4505 or 0406 (may be in cell D1 and D2).
I know the duration, for example 10 weeks and 12 weeks (may be in cell C1
and C2).
What I am looking for help is a formula for how to find a start date.

Cell D1 = 4505
Cell C1 = 10
Cell B1 = 3505 (what is the formula to get this).

Cell D2 = 0406
Cell C2 = 12
Cell B2 = 4405 (what is the formula to get this).

I deal with about 100 to 150 of these in each spreadsheet.

I am using MS Excel 97