Can any one advise me on the following.
How do I create a simple spreadsheet to return dates from a set criteria.

Example
I want to enter a start date, then the number of weeks of the period (i.e
26weeks)
This will return a date DD/MM/YY
I wouldalso like to be able to enter a offset figure of +/- 1,2, or 3 ....days
The offset criteria providing me with the date I want.
For Example
Date DD/MM/YY - 1 day ....etc
or
Date DD/MM/YY + 1 day.....etc

Question
The offset I could possible select from say a validation list?


Taking the spreadsheet a little further
How would I incorporate the periods in Weeks / Months / Years
For Example
Select Period from a Validation list....weeks or Months or Years
Enter a Start Date
Enter Number of Weeks / Months or Years I want to count ahead
Enter the offset number......the criteria to provide the date I want.

Any help would be great
Even if anyone can provide a sample .Xls that I could study would be
fantastic.

Thanks in advance

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

Thanks for the reply Gary
I think there is something wrong with the parenthesis with this formula as
it returns an error message.
Any further suggestions?

"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

Are you using US regional settings? If not you might try by replacing the
commas with semi colon since the formula should work


--
Regards,

Peo Sjoblom


"Dermot" wrote in message
...
Thanks for the reply Gary
I think there is something wrong with the parenthesis with this formula as
it returns an error message.
Any further suggestions?

"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

It's never desperately helpful to say "it returns an error message". Youre
more likely to get help if you tell the group WHAT error message you're
getting.

Why do you suggest a problem with parenthseses? They look to me as if they
match, and Excel doesn't report an error for me.

The most likely problem of which I could think is that perhaps your regional
settings expect a semi-colon as a list separator, whereas the formula as
quoted includes commas. Have you checked your regional settings? Have you
tried changing commas to semi-colons (or whatever your settings are
expecting)?
--
David Biddulph

"Dermot" wrote in message
...
Thanks for the reply Gary
I think there is something wrong with the parenthesis with this formula as
it returns an error message.
Any further suggestions?

"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

Hi Peo
Thanks for your comments.
I am using UK regional settings.
It is now working with the commas as you have all explained, I didn't need
to change to colon separators.
Oddly I cannot recreate the problem I was having earlier.....I copy and
pasted the formula the first time.....this time I entered it manually.....and
got a result.
Thanks

"Peo Sjoblom" wrote:

Are you using US regional settings? If not you might try by replacing the
commas with semi colon since the formula should work


--
Regards,

Peo Sjoblom


"Dermot" wrote in message
...
Thanks for the reply Gary
I think there is something wrong with the parenthesis with this formula as
it returns an error message.
Any further suggestions?

"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

David
Thanks for your comments
I though I had a parenthesis error because of th wording in the message.
I initially copy and pasted the formula...when I got the error.....returning
to the forum to view your response and the previous one.......I entered the
formula with comma separators......and got a result......I' tried copy and
pasting again and and that works too......so I am uncertain why I got the
error.....in my efforts to introduce an error to find out what I'd done
wrong.... I cannot recreate the original eror message.
More importantly.....my question has been answered...and I am grateful for
all contributions.
Cheers


"David Biddulph" wrote:

It's never desperately helpful to say "it returns an error message". Youre
more likely to get help if you tell the group WHAT error message you're
getting.

Why do you suggest a problem with parenthseses? They look to me as if they
match, and Excel doesn't report an error for me.

The most likely problem of which I could think is that perhaps your regional
settings expect a semi-colon as a list separator, whereas the formula as
quoted includes commas. Have you checked your regional settings? Have you
tried changing commas to semi-colons (or whatever your settings are
expecting)?
--
David Biddulph

"Dermot" wrote in message
...
Thanks for the reply Gary
I think there is something wrong with the parenthesis with this formula as
it returns an error message.
Any further suggestions?

"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

Hi Gary
I have a further question relating to this topic.
I am trying to understand the relationship between working in periods of
Weeks and Periods of months....but can't determine how to get the same result
using the same start date.
For Example

If I want
6 months minus a day
Start Date: 16/05/2007 + 6 months - 1day
Result: 15/05/2007

Doing the same in Weeks
Using the formula given with the following data
Start Date: 16/11/06
N0 of Weeks: 26
Offset: -1
Result: 16/05/07
I expected to get the 15/5/2007 as the result.

I assume this may be because some months have 5 weeks / different number of
days.

Can you clarify what I am over looking here and how I could demonstrate it
in excel.
Thanks in advance








"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

For example:

START 16-Nov-2006
1 week later 23-Nov-2006
2 weeks later 30-Nov-2006
3 weeks later 7-Dec-2006
4 weeks later 14-Dec-2006
5 weeks later 21-Dec-2006
6 weeks later 28-Dec-2006
7 weeks later 4-Jan-2007
8 weeks later 11-Jan-2007
9 weeks later 18-Jan-2007
10 weeks later 25-Jan-2007
11 weeks later 1-Feb-2007
12 weeks later 8-Feb-2007
13 weeks later 15-Feb-2007
14 weeks later 22-Feb-2007
15 weeks later 1-Mar-2007
16 weeks later 8-Mar-2007
17 weeks later 15-Mar-2007
18 weeks later 22-Mar-2007
19 weeks later 29-Mar-2007
20 weeks later 5-Apr-2007
21 weeks later 12-Apr-2007
22 weeks later 19-Apr-2007
23 weeks later 26-Apr-2007
24 weeks later 3-May-2007
25 weeks later 10-May-2007
26 weeks later 17-May-2007
less one day 16-May-2007


Each date is seven days (1 week) more than the previous date (except, of
course, the last entry)
--
Gary''s Student - gsnu200725


"Dermot" wrote:

Hi Gary
I have a further question relating to this topic.
I am trying to understand the relationship between working in periods of
Weeks and Periods of months....but can't determine how to get the same result
using the same start date.
For Example

If I want
6 months minus a day
Start Date: 16/05/2007 + 6 months - 1day
Result: 15/05/2007

Doing the same in Weeks
Using the formula given with the following data
Start Date: 16/11/06
N0 of Weeks: 26
Offset: -1
Result: 16/05/07
I expected to get the 15/5/2007 as the result.

I assume this may be because some months have 5 weeks / different number of
days.

Can you clarify what I am over looking here and how I could demonstrate it
in excel.
Thanks in advance








"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

Hi,
I am sorry to Go on, please be patient... advise further with me:
Confirm the following: So I am correct to say that although I am considering
half year periods, the calculations are not entirely the same?
a) 1 year = 12 months so half year period is 6months - 1 day......wanted
result
b) 1 Year = 52 Weeks so half year period is 26 Weeks - 1 day
C) 1 Year = 365 days so half year period is 182.5 days - 1 day

Thanks in advance

PS: I am not even sure why I would be taking 1 day off the period....would
this be standard practice when working with financial time periods?




"Gary''s Student" wrote:

For example:

START 16-Nov-2006
1 week later 23-Nov-2006
2 weeks later 30-Nov-2006
3 weeks later 7-Dec-2006
4 weeks later 14-Dec-2006
5 weeks later 21-Dec-2006
6 weeks later 28-Dec-2006
7 weeks later 4-Jan-2007
8 weeks later 11-Jan-2007
9 weeks later 18-Jan-2007
10 weeks later 25-Jan-2007
11 weeks later 1-Feb-2007
12 weeks later 8-Feb-2007
13 weeks later 15-Feb-2007
14 weeks later 22-Feb-2007
15 weeks later 1-Mar-2007
16 weeks later 8-Mar-2007
17 weeks later 15-Mar-2007
18 weeks later 22-Mar-2007
19 weeks later 29-Mar-2007
20 weeks later 5-Apr-2007
21 weeks later 12-Apr-2007
22 weeks later 19-Apr-2007
23 weeks later 26-Apr-2007
24 weeks later 3-May-2007
25 weeks later 10-May-2007
26 weeks later 17-May-2007
less one day 16-May-2007


Each date is seven days (1 week) more than the previous date (except, of
course, the last entry)
--
Gary''s Student - gsnu200725


"Dermot" wrote:

Hi Gary
I have a further question relating to this topic.
I am trying to understand the relationship between working in periods of
Weeks and Periods of months....but can't determine how to get the same result
using the same start date.
For Example

If I want
6 months minus a day
Start Date: 16/05/2007 + 6 months - 1day
Result: 15/05/2007

Doing the same in Weeks
Using the formula given with the following data
Start Date: 16/11/06
N0 of Weeks: 26
Offset: -1
Result: 16/05/07
I expected to get the 15/5/2007 as the result.

I assume this may be because some months have 5 weeks / different number of
days.

Can you clarify what I am over looking here and how I could demonstrate it
in excel.
Thanks in advance








"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

You need the -1 only in certain cases. For example:

Say on Jan 1 we want the date one half year from Jan 1. We enter:
1/1/2007 in A1

in a second cell we enter:
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))


In a third cell we enter:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*26)

We innocently expect the second and third cells to be the same (after all,
are not 6 months and 26 weeks the same??)

The second cell shows July 1 and the third cell shows July 2!!

Once you look at a real, physical, calendar, the reason is clear. Moving
week-by-week puts us at a different final destination than moving
month-by-month.

The real, physical, calendar will make it much clearer than my clumsy
explanation.
--
Gary''s Student - gsnu200725


"Dermot" wrote:

Hi,
I am sorry to Go on, please be patient... advise further with me:
Confirm the following: So I am correct to say that although I am considering
half year periods, the calculations are not entirely the same?
a) 1 year = 12 months so half year period is 6months - 1 day......wanted
result
b) 1 Year = 52 Weeks so half year period is 26 Weeks - 1 day
C) 1 Year = 365 days so half year period is 182.5 days - 1 day

Thanks in advance

PS: I am not even sure why I would be taking 1 day off the period....would
this be standard practice when working with financial time periods?




"Gary''s Student" wrote:

For example:

START 16-Nov-2006
1 week later 23-Nov-2006
2 weeks later 30-Nov-2006
3 weeks later 7-Dec-2006
4 weeks later 14-Dec-2006
5 weeks later 21-Dec-2006
6 weeks later 28-Dec-2006
7 weeks later 4-Jan-2007
8 weeks later 11-Jan-2007
9 weeks later 18-Jan-2007
10 weeks later 25-Jan-2007
11 weeks later 1-Feb-2007
12 weeks later 8-Feb-2007
13 weeks later 15-Feb-2007
14 weeks later 22-Feb-2007
15 weeks later 1-Mar-2007
16 weeks later 8-Mar-2007
17 weeks later 15-Mar-2007
18 weeks later 22-Mar-2007
19 weeks later 29-Mar-2007
20 weeks later 5-Apr-2007
21 weeks later 12-Apr-2007
22 weeks later 19-Apr-2007
23 weeks later 26-Apr-2007
24 weeks later 3-May-2007
25 weeks later 10-May-2007
26 weeks later 17-May-2007
less one day 16-May-2007


Each date is seven days (1 week) more than the previous date (except, of
course, the last entry)
--
Gary''s Student - gsnu200725


"Dermot" wrote:

Hi Gary
I have a further question relating to this topic.
I am trying to understand the relationship between working in periods of
Weeks and Periods of months....but can't determine how to get the same result
using the same start date.
For Example

If I want
6 months minus a day
Start Date: 16/05/2007 + 6 months - 1day
Result: 15/05/2007

Doing the same in Weeks
Using the formula given with the following data
Start Date: 16/11/06
N0 of Weeks: 26
Offset: -1
Result: 16/05/07
I expected to get the 15/5/2007 as the result.

I assume this may be because some months have 5 weeks / different number of
days.

Can you clarify what I am over looking here and how I could demonstrate it
in excel.
Thanks in advance








"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723

Hi Gary's Student
Thanks for the further explanation.
I don't think your explanation is any more clumsy than my terrible
explanation of what I was asking!
You have confirmed exactly what I was thinking.......they are two different
calculations as they involve a different number of days when physically
counted out on the calendar.

I was asked to used periods of 26 weeks - day.........where really I should
have been told to use 6months - a day to get the required date.

Nobody seemed to understand that they were different
calculations.....although I don't think I explained myself very well.

Alls well that ends well
Thanks for your time.

"Gary''s Student" wrote:

You need the -1 only in certain cases. For example:

Say on Jan 1 we want the date one half year from Jan 1. We enter:
1/1/2007 in A1

in a second cell we enter:
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))


In a third cell we enter:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*26)

We innocently expect the second and third cells to be the same (after all,
are not 6 months and 26 weeks the same??)

The second cell shows July 1 and the third cell shows July 2!!

Once you look at a real, physical, calendar, the reason is clear. Moving
week-by-week puts us at a different final destination than moving
month-by-month.

The real, physical, calendar will make it much clearer than my clumsy
explanation.
--
Gary''s Student - gsnu200725


"Dermot" wrote:

Hi,
I am sorry to Go on, please be patient... advise further with me:
Confirm the following: So I am correct to say that although I am considering
half year periods, the calculations are not entirely the same?
a) 1 year = 12 months so half year period is 6months - 1 day......wanted
result
b) 1 Year = 52 Weeks so half year period is 26 Weeks - 1 day
C) 1 Year = 365 days so half year period is 182.5 days - 1 day

Thanks in advance

PS: I am not even sure why I would be taking 1 day off the period....would
this be standard practice when working with financial time periods?




"Gary''s Student" wrote:

For example:

START 16-Nov-2006
1 week later 23-Nov-2006
2 weeks later 30-Nov-2006
3 weeks later 7-Dec-2006
4 weeks later 14-Dec-2006
5 weeks later 21-Dec-2006
6 weeks later 28-Dec-2006
7 weeks later 4-Jan-2007
8 weeks later 11-Jan-2007
9 weeks later 18-Jan-2007
10 weeks later 25-Jan-2007
11 weeks later 1-Feb-2007
12 weeks later 8-Feb-2007
13 weeks later 15-Feb-2007
14 weeks later 22-Feb-2007
15 weeks later 1-Mar-2007
16 weeks later 8-Mar-2007
17 weeks later 15-Mar-2007
18 weeks later 22-Mar-2007
19 weeks later 29-Mar-2007
20 weeks later 5-Apr-2007
21 weeks later 12-Apr-2007
22 weeks later 19-Apr-2007
23 weeks later 26-Apr-2007
24 weeks later 3-May-2007
25 weeks later 10-May-2007
26 weeks later 17-May-2007
less one day 16-May-2007


Each date is seven days (1 week) more than the previous date (except, of
course, the last entry)
--
Gary''s Student - gsnu200725


"Dermot" wrote:

Hi Gary
I have a further question relating to this topic.
I am trying to understand the relationship between working in periods of
Weeks and Periods of months....but can't determine how to get the same result
using the same start date.
For Example

If I want
6 months minus a day
Start Date: 16/05/2007 + 6 months - 1day
Result: 15/05/2007

Doing the same in Weeks
Using the formula given with the following data
Start Date: 16/11/06
N0 of Weeks: 26
Offset: -1
Result: 16/05/07
I expected to get the 15/5/2007 as the result.

I assume this may be because some months have 5 weeks / different number of
days.

Can you clarify what I am over looking here and how I could demonstrate it
in excel.
Thanks in advance








"Gary''s Student" wrote:

put the start date in A1, the number of weeks in B1 and the offset in C1
In D1 put:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+7*B1+C1)
and format as dd/mm/yy
--
Gary''s Student - gsnu200723