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" <(E-Mail Removed)> wrote in message
news:C0FD0583-171F-48EA-BB6E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E6239E33-15F3-4E16-ADF7-(E-Mail Removed)...
>> > 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)
>> >
>> > "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" <(E-Mail Removed)> wrote in message
>> >> > news:F1CD163E-2F72-4C4B-AB3C-(E-Mail Removed)...
>> >> > > 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?
>> >> > >
>> >> > >
>> >> >
>> >> >
>>
>>
|
Similar Threads
