Assuming I have a Date column D

Question 1
When a date is entered into it, I would like:
Cell E = x Weeks
Cell F = y month - 1 day
Cell G = z Months - 1 day

Would I use an IF Then statement to achieve this?
Could anyone provide a VBA example I could work from?

I am not quite sure what you want for Cell F... what is "month" (singular)
minus a day supposed to be? Also, the weeks and months (plural)... are those
from the beginning of the year? If so, how are the weeks measured... each 7
day period starting from January 1st? Or is week #1 the week January 1st
falls in, no matter what day of the week that is? Or do you have one of
those other criteria for determining which week is week #1?

Rick


"Dermot" wrote in message
...
Assuming I have a Date column D

Question 1
When a date is entered into it, I would like:
Cell E = x Weeks
Cell F = y month - 1 day
Cell G = z Months - 1 day

Would I use an IF Then statement to achieve this?
Could anyone provide a VBA example I could work from?

Hi Rick
Thanks for the reply.
My explanation wasn't that great.....

The dates x y and z are relative to the Date in Column D.

For Example:
Cell E = 10 Oct 07 to 14 November 2007 [5 weeks (35 Days)]
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Cell G = 10 Oct 07 to 10 Mar 09 = (18 months - 1 day)

"Rick Rothstein (MVP - VB)" wrote:

I am not quite sure what you want for Cell F... what is "month" (singular)
minus a day supposed to be? Also, the weeks and months (plural)... are those
from the beginning of the year? If so, how are the weeks measured... each 7
day period starting from January 1st? Or is week #1 the week January 1st
falls in, no matter what day of the week that is? Or do you have one of
those other criteria for determining which week is week #1?

Rick


"Dermot" wrote in message
...
Assuming I have a Date column D

Question 1
When a date is entered into it, I would like:
Cell E = x Weeks
Cell F = y month - 1 day
Cell G = z Months - 1 day

Would I use an IF Then statement to achieve this?
Could anyone provide a VBA example I could work from?

Sorry Rick,
I made an error the
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Should read:
Cell F = 10 Oct 07 to 9 April 07 (6 Months - 1 day)

"Dermot" wrote:

Hi Rick
Thanks for the reply.
My explanation wasn't that great.....

The dates x y and z are relative to the Date in Column D.

For Example:
Cell E = 10 Oct 07 to 14 November 2007 [5 weeks (35 Days)]
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Cell G = 10 Oct 07 to 10 Mar 09 = (18 months - 1 day)

"Rick Rothstein (MVP - VB)" wrote:

I am not quite sure what you want for Cell F... what is "month" (singular)
minus a day supposed to be? Also, the weeks and months (plural)... are those
from the beginning of the year? If so, how are the weeks measured... each 7
day period starting from January 1st? Or is week #1 the week January 1st
falls in, no matter what day of the week that is? Or do you have one of
those other criteria for determining which week is week #1?

Rick


"Dermot" wrote in message
...
Assuming I have a Date column D

Question 1
When a date is entered into it, I would like:
Cell E = x Weeks
Cell F = y month - 1 day
Cell G = z Months - 1 day

Would I use an IF Then statement to achieve this?
Could anyone provide a VBA example I could work from?

Hi Rick
Answers within your text€¦€¦€¦..

Sorry, but I have some follow up questions for you.

Each example you gave shows two dates... Which one of them is in Column D?
Examples:
Column D = 10 Oct 07
Cell E = 14 November 2007 [5 weeks (35 Days)]
Cell F = 9 April 07 (6 Months - 1 day)
Cell G = 9 Mar 09 = (18 months - 1 day)


What cell is the other date in and which cell do you want the "answers" to
go in? Answer: As above

For the answer in weeks... Will the date range always be a whole number of
weeks? If not, what to you want done with the fractional part... truncate it
or round it?

Answer:
Round to full day: if I entered Wed 19 September 07 in Cell D1,
I would count 5 Weeks and my result would be 24 October 07 in Cell E1 I
would also want 18 March 07 to be automatically entered in Cell F

For the answer in months... are you asking for a whole number of months with
a plus or minus number of days after it?

Answer
I am viewing 6 month periods as say (10th of start month) to (10th of 6th
month minus 1 day)
So that would be 10th to 9th€¦€¦if you know what I mean?

I hope this helps Rick

Rephrased another way:
Whatever date I enter in cell D, I would like the period dates to
automatically enter into the adjacent cells€¦..this would save working them
out and having to manually enter them.

I am beginning to think it may be easier to just enter them manually
although the automation would be attractive in terms of efficiency.

Weeks Periods = 7 days

Month period = 29th to 29th-1day therefore 29th to 28th of the following
month.

Thanks for any suggestions

Rick


"Rick Rothstein (MVP - VB)" wrote:

Sorry, but I have some follow up questions for you.

Each example you gave shows two dates... Which one of them is in Column D?
What cell is the other date in and which cell do you want the "answers" to
go in?

For the answer in weeks... Will the date range always be a whole number of
weeks? If not, what to you want done with the fractional part... truncate it
or round it?

For the answer in months... are you asking for a whole number of months with
a plus or minus number of days after it? This raises some issues... what is
a whole month.... the day number in one month to the day number in another
month? What do you do about "end of month" differences? I'll use an extreme
example... What answer would you expect for the following date ranges...
January 29th to February 28th, January 30th to February 28th, January 31st
to February 28th and most importantly, after those, what are your answers
for these... January 29th to March 1st, January 30th to March 1st, January
31st to March 1st?

Rick


"Dermot" wrote in message
...
Sorry Rick,
I made an error the
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Should read:
Cell F = 10 Oct 07 to 9 April 07 (6 Months - 1 day)

"Dermot" wrote:

Hi Rick
Thanks for the reply.
My explanation wasn't that great.....

The dates x y and z are relative to the Date in Column D.

For Example:
Cell E = 10 Oct 07 to 14 November 2007 [5 weeks (35 Days)]
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Cell G = 10 Oct 07 to 10 Mar 09 = (18 months - 1 day)

"Rick Rothstein (MVP - VB)" wrote:

I am not quite sure what you want for Cell F... what is "month"
(singular)
minus a day supposed to be? Also, the weeks and months (plural)... are
those
from the beginning of the year? If so, how are the weeks measured...
each 7
day period starting from January 1st? Or is week #1 the week January
1st
falls in, no matter what day of the week that is? Or do you have one of
those other criteria for determining which week is week #1?

Rick


"Dermot" wrote in message
...
Assuming I have a Date column D

Question 1
When a date is entered into it, I would like:
Cell E = x Weeks
Cell F = y month - 1 day
Cell G = z Months - 1 day

Would I use an IF Then statement to achieve this?
Could anyone provide a VBA example I could work from?

Okay, I think I understand what you want now (your example for Cell G should
have been 9 April 09, not March, right?). Try these formulas in E1, F1, G1
and then copy them down as far as you need to...

E1: =IF(D1="","",D1+35)
F1: =IF(D1="","",EDATE(D1,6)-1)
G1: =IF(D1="","",EDATE(D1,18)-1)

You might have to format your cells as Date for them to show correctly. By
the way, the EDATE function that I am using requires you to select the
Analysis ToolPak (from Tools/Add-Ins in Excel's menu bar).

Rick



"Dermot" wrote in message
...
Hi Rick
Answers within your text€¦€¦€¦..

Sorry, but I have some follow up questions for you.

Each example you gave shows two dates... Which one of them is in Column D?
Examples:
Column D = 10 Oct 07
Cell E = 14 November 2007 [5 weeks (35 Days)]
Cell F = 9 April 07 (6 Months - 1 day)
Cell G = 9 Mar 09 = (18 months - 1 day)


What cell is the other date in and which cell do you want the "answers" to
go in? Answer: As above

For the answer in weeks... Will the date range always be a whole number of
weeks? If not, what to you want done with the fractional part... truncate
it
or round it?

Answer:
Round to full day: if I entered Wed 19 September 07 in Cell D1,
I would count 5 Weeks and my result would be 24 October 07 in Cell E1 I
would also want 18 March 07 to be automatically entered in Cell F

For the answer in months... are you asking for a whole number of months
with
a plus or minus number of days after it?

Answer
I am viewing 6 month periods as say (10th of start month) to (10th of 6th
month minus 1 day)
So that would be 10th to 9th€¦€¦if you know what I mean?

I hope this helps Rick

Rephrased another way:
Whatever date I enter in cell D, I would like the period dates to
automatically enter into the adjacent cells€¦..this would save working them
out and having to manually enter them.

I am beginning to think it may be easier to just enter them manually
although the automation would be attractive in terms of efficiency.

Weeks Periods = 7 days

Month period = 29th to 29th-1day therefore 29th to 28th of the following
month.

Thanks for any suggestions

Rick


"Rick Rothstein (MVP - VB)" wrote:

Sorry, but I have some follow up questions for you.

Each example you gave shows two dates... Which one of them is in Column
D?
What cell is the other date in and which cell do you want the "answers"
to
go in?

For the answer in weeks... Will the date range always be a whole number
of
weeks? If not, what to you want done with the fractional part... truncate
it
or round it?

For the answer in months... are you asking for a whole number of months
with
a plus or minus number of days after it? This raises some issues... what
is
a whole month.... the day number in one month to the day number in
another
month? What do you do about "end of month" differences? I'll use an
extreme
example... What answer would you expect for the following date ranges...
January 29th to February 28th, January 30th to February 28th, January
31st
to February 28th and most importantly, after those, what are your answers
for these... January 29th to March 1st, January 30th to March 1st,
January
31st to March 1st?

Rick


"Dermot" wrote in message
...
Sorry Rick,
I made an error the
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Should read:
Cell F = 10 Oct 07 to 9 April 07 (6 Months - 1 day)

"Dermot" wrote:

Hi Rick
Thanks for the reply.
My explanation wasn't that great.....

The dates x y and z are relative to the Date in Column D.

For Example:
Cell E = 10 Oct 07 to 14 November 2007 [5 weeks (35 Days)]
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Cell G = 10 Oct 07 to 10 Mar 09 = (18 months - 1 day)

"Rick Rothstein (MVP - VB)" wrote:

I am not quite sure what you want for Cell F... what is "month"
(singular)
minus a day supposed to be? Also, the weeks and months (plural)...
are
those
from the beginning of the year? If so, how are the weeks measured...
each 7
day period starting from January 1st? Or is week #1 the week January
1st
falls in, no matter what day of the week that is? Or do you have one
of
those other criteria for determining which week is week #1?

Rick


"Dermot" wrote in message
...
Assuming I have a Date column D

Question 1
When a date is entered into it, I would like:
Cell E = x Weeks
Cell F = y month - 1 day
Cell G = z Months - 1 day

Would I use an IF Then statement to achieve this?
Could anyone provide a VBA example I could work from?

Thanks Rick that's great.

I'll try this out

Cheers

Dermot

"Rick Rothstein (MVP - VB)" wrote:

Okay, I think I understand what you want now (your example for Cell G should
have been 9 April 09, not March, right?). Try these formulas in E1, F1, G1
and then copy them down as far as you need to...

E1: =IF(D1="","",D1+35)
F1: =IF(D1="","",EDATE(D1,6)-1)
G1: =IF(D1="","",EDATE(D1,18)-1)

You might have to format your cells as Date for them to show correctly. By
the way, the EDATE function that I am using requires you to select the
Analysis ToolPak (from Tools/Add-Ins in Excel's menu bar).

Rick



"Dermot" wrote in message
...
Hi Rick
Answers within your text€¦€¦€¦..

Sorry, but I have some follow up questions for you.

Each example you gave shows two dates... Which one of them is in Column D?
Examples:
Column D = 10 Oct 07
Cell E = 14 November 2007 [5 weeks (35 Days)]
Cell F = 9 April 07 (6 Months - 1 day)
Cell G = 9 Mar 09 = (18 months - 1 day)


What cell is the other date in and which cell do you want the "answers" to
go in? Answer: As above

For the answer in weeks... Will the date range always be a whole number of
weeks? If not, what to you want done with the fractional part... truncate
it
or round it?

Answer:
Round to full day: if I entered Wed 19 September 07 in Cell D1,
I would count 5 Weeks and my result would be 24 October 07 in Cell E1 I
would also want 18 March 07 to be automatically entered in Cell F

For the answer in months... are you asking for a whole number of months
with
a plus or minus number of days after it?

Answer
I am viewing 6 month periods as say (10th of start month) to (10th of 6th
month minus 1 day)
So that would be 10th to 9th€¦€¦if you know what I mean?

I hope this helps Rick

Rephrased another way:
Whatever date I enter in cell D, I would like the period dates to
automatically enter into the adjacent cells€¦..this would save working them
out and having to manually enter them.

I am beginning to think it may be easier to just enter them manually
although the automation would be attractive in terms of efficiency.

Weeks Periods = 7 days

Month period = 29th to 29th-1day therefore 29th to 28th of the following
month.

Thanks for any suggestions

Rick


"Rick Rothstein (MVP - VB)" wrote:

Sorry, but I have some follow up questions for you.

Each example you gave shows two dates... Which one of them is in Column
D?
What cell is the other date in and which cell do you want the "answers"
to
go in?

For the answer in weeks... Will the date range always be a whole number
of
weeks? If not, what to you want done with the fractional part... truncate
it
or round it?

For the answer in months... are you asking for a whole number of months
with
a plus or minus number of days after it? This raises some issues... what
is
a whole month.... the day number in one month to the day number in
another
month? What do you do about "end of month" differences? I'll use an
extreme
example... What answer would you expect for the following date ranges...
January 29th to February 28th, January 30th to February 28th, January
31st
to February 28th and most importantly, after those, what are your answers
for these... January 29th to March 1st, January 30th to March 1st,
January
31st to March 1st?

Rick


"Dermot" wrote in message
...
Sorry Rick,
I made an error the
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Should read:
Cell F = 10 Oct 07 to 9 April 07 (6 Months - 1 day)

"Dermot" wrote:

Hi Rick
Thanks for the reply.
My explanation wasn't that great.....

The dates x y and z are relative to the Date in Column D.

For Example:
Cell E = 10 Oct 07 to 14 November 2007 [5 weeks (35 Days)]
Cell F = 10 Oct 07 to 9 Nov 07 = (1 month - 1 Day)
Cell G = 10 Oct 07 to 10 Mar 09 = (18 months - 1 day)

"Rick Rothstein (MVP - VB)" wrote:

I am not quite sure what you want for Cell F... what is "month"
(singular)
minus a day supposed to be? Also, the weeks and months (plural)...
are
those
from the beginning of the year? If so, how are the weeks measured...
each 7
day period starting from January 1st? Or is week #1 the week January
1st
falls in, no matter what day of the week that is? Or do you have one
of
those other criteria for determining which week is week #1?

Rick


"Dermot" wrote in message
...
Assuming I have a Date column D

Question 1
When a date is entered into it, I would like:
Cell E = x Weeks
Cell F = y month - 1 day
Cell G = z Months - 1 day

Would I use an IF Then statement to achieve this?
Could anyone provide a VBA example I could work from?