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) said:
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
Sorry Rick,
I made an error there:
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)
:
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)
:
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
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?
