fill series - I think!

Laurina · 04-25-2006, 10:40 AM

I need to create a planner - previously have manually inputted all the Mon
Tue dates and click and dragged the rest but wondered if there was a better
way. Need M, T, W, T, F dates skip weekend, start again.

Have tried doing it by dragging over a 2 week period to see if the pattern
is recognised but it doesn't work.

Laurina

**Petitboeuf** · 04-25-2006, 10:48 AM

Laurina

I am using Excel 2003 and I have tried the following:

Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday Thursday Friday

in columns A to J.

When i select the lot then drag to the right it starts from Monday then end the week on Friday, then starts again with Monday, etc...

Is this what you are after?

Laurina · 04-25-2006, 11:20 AM

Not quite. Here's an example

M 28-Sep
T 29-Sep
W 30-Sep
Th 1-Oct
F 2-Oct
M 4-Oct
T 5-Oct
W 6-Oct
Th 7-Oct
F 8-Oct

"Petitboeuf" wrote:

>
> Laurina
>
> I am using Excel 2003 and I have tried the following:
>
> Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday Thursday Friday
>
> in columns A to J.
>
> When i select the lot then drag to the right it starts from Monday then
> end the week on Friday, then starts again with Monday, etc...
>
> Is this what you are after?
>
>
> --
> Petitboeuf
> ------------------------------------------------------------------------
> Petitboeuf's Profile: http://www.excelforum.com/member.php...o&userid=10602
> View this thread: http://www.excelforum.com/showthread...hreadid=535963
>
>

Roger Govier · 04-25-2006, 11:55 AM

Hi Laurina

Try using the Workday() function.

With your first date in A1, in A2 enter
=WORKDAY(A1,1)
Copy down and you will just get the workdays of each week.
If you want to exclude Public Holidays from the list, then pout those
dates in a range of cells and either name the range as Holidays or refer
directly to the range of cells holding the dates with the following
modified formula
=WORKDAY(A1,1,holidays) or = WORKDAY(A1,1,$E1:$E10) where E1:E10 holds
the range of holiday dates.

--
Regards

Roger Govier

"Laurina" <[email protected]> wrote in message
news:[email protected]...
> Not quite. Here's an example
>
> M 28-Sep
> T 29-Sep
> W 30-Sep
> Th 1-Oct
> F 2-Oct
> M 4-Oct
> T 5-Oct
> W 6-Oct
> Th 7-Oct
> F 8-Oct
>
>
> "Petitboeuf" wrote:
>
>>
>> Laurina
>>
>> I am using Excel 2003 and I have tried the following:
>>
>> Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday
>> Thursday Friday
>>
>> in columns A to J.
>>
>> When i select the lot then drag to the right it starts from Monday
>> then
>> end the week on Friday, then starts again with Monday, etc...
>>
>> Is this what you are after?
>>
>>
>> --
>> Petitboeuf
>> ------------------------------------------------------------------------
>> Petitboeuf's Profile:
>> http://www.excelforum.com/member.php...o&userid=10602
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=535963
>>
>>

Laurina · 04-25-2006, 12:30 PM

Thanks for that but the file isn't recognising the workday bit - comes up
with #name and then #ref.

"Roger Govier" wrote:

> Hi Laurina
>
> Try using the Workday() function.
>
> With your first date in A1, in A2 enter
> =WORKDAY(A1,1)
> Copy down and you will just get the workdays of each week.
> If you want to exclude Public Holidays from the list, then pout those
> dates in a range of cells and either name the range as Holidays or refer
> directly to the range of cells holding the dates with the following
> modified formula
> =WORKDAY(A1,1,holidays) or = WORKDAY(A1,1,$E1:$E10) where E1:E10 holds
> the range of holiday dates.
>
> --
> Regards
>
> Roger Govier
>
>
> "Laurina" <[email protected]> wrote in message
> news:[email protected]...
> > Not quite. Here's an example
> >
> > M 28-Sep
> > T 29-Sep
> > W 30-Sep
> > Th 1-Oct
> > F 2-Oct
> > M 4-Oct
> > T 5-Oct
> > W 6-Oct
> > Th 7-Oct
> > F 8-Oct
> >
> >
> > "Petitboeuf" wrote:
> >
> >>
> >> Laurina
> >>
> >> I am using Excel 2003 and I have tried the following:
> >>
> >> Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday
> >> Thursday Friday
> >>
> >> in columns A to J.
> >>
> >> When i select the lot then drag to the right it starts from Monday
> >> then
> >> end the week on Friday, then starts again with Monday, etc...
> >>
> >> Is this what you are after?
> >>
> >>
> >> --
> >> Petitboeuf
> >> ------------------------------------------------------------------------
> >> Petitboeuf's Profile:
> >> http://www.excelforum.com/member.php...o&userid=10602
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=535963
> >>
> >>
>
>
>

Roger Govier · 04-25-2006, 01:10 PM

Hi Laurina

I should have added that you need the Analysis Toolpak loaded.
Tools>Addins> and check Analysis Toolpak

--
Regards

Roger Govier

"Laurina" <[email protected]> wrote in message
news:[email protected]...
> Thanks for that but the file isn't recognising the workday bit - comes
> up
> with #name and then #ref.
>
> "Roger Govier" wrote:
>
>> Hi Laurina
>>
>> Try using the Workday() function.
>>
>> With your first date in A1, in A2 enter
>> =WORKDAY(A1,1)
>> Copy down and you will just get the workdays of each week.
>> If you want to exclude Public Holidays from the list, then pout those
>> dates in a range of cells and either name the range as Holidays or
>> refer
>> directly to the range of cells holding the dates with the following
>> modified formula
>> =WORKDAY(A1,1,holidays) or = WORKDAY(A1,1,$E1:$E10) where E1:E10
>> holds
>> the range of holiday dates.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Laurina" <[email protected]> wrote in message
>> news:[email protected]...
>> > Not quite. Here's an example
>> >
>> > M 28-Sep
>> > T 29-Sep
>> > W 30-Sep
>> > Th 1-Oct
>> > F 2-Oct
>> > M 4-Oct
>> > T 5-Oct
>> > W 6-Oct
>> > Th 7-Oct
>> > F 8-Oct
>> >
>> >
>> > "Petitboeuf" wrote:
>> >
>> >>
>> >> Laurina
>> >>
>> >> I am using Excel 2003 and I have tried the following:
>> >>
>> >> Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday
>> >> Thursday Friday
>> >>
>> >> in columns A to J.
>> >>
>> >> When i select the lot then drag to the right it starts from Monday
>> >> then
>> >> end the week on Friday, then starts again with Monday, etc...
>> >>
>> >> Is this what you are after?
>> >>
>> >>
>> >> --
>> >> Petitboeuf
>> >> ------------------------------------------------------------------------
>> >> Petitboeuf's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=10602
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=535963
>> >>
>> >>
>>
>>
>>

Gord Dibben · 04-25-2006, 02:15 PM

Laurina

The WORKDAY Function is from the Analysis Toolpak Add-in.

Load it through Tools>Add-ins to eliminate the #NAME! error.

Gord Dibben MS Excel MVP

On Tue, 25 Apr 2006 09:25:01 -0700, Laurina <[email protected]>
wrote:

>Thanks for that but the file isn't recognising the workday bit - comes up
>with #name and then #ref.
>
>"Roger Govier" wrote:
>
>> Hi Laurina
>>
>> Try using the Workday() function.
>>
>> With your first date in A1, in A2 enter
>> =WORKDAY(A1,1)
>> Copy down and you will just get the workdays of each week.
>> If you want to exclude Public Holidays from the list, then pout those
>> dates in a range of cells and either name the range as Holidays or refer
>> directly to the range of cells holding the dates with the following
>> modified formula
>> =WORKDAY(A1,1,holidays) or = WORKDAY(A1,1,$E1:$E10) where E1:E10 holds
>> the range of holiday dates.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Laurina" <[email protected]> wrote in message
>> news:[email protected]...
>> > Not quite. Here's an example
>> >
>> > M 28-Sep
>> > T 29-Sep
>> > W 30-Sep
>> > Th 1-Oct
>> > F 2-Oct
>> > M 4-Oct
>> > T 5-Oct
>> > W 6-Oct
>> > Th 7-Oct
>> > F 8-Oct
>> >
>> >
>> > "Petitboeuf" wrote:
>> >
>> >>
>> >> Laurina
>> >>
>> >> I am using Excel 2003 and I have tried the following:
>> >>
>> >> Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday
>> >> Thursday Friday
>> >>
>> >> in columns A to J.
>> >>
>> >> When i select the lot then drag to the right it starts from Monday
>> >> then
>> >> end the week on Friday, then starts again with Monday, etc...
>> >>
>> >> Is this what you are after?
>> >>
>> >>
>> >> --
>> >> Petitboeuf
>> >> ------------------------------------------------------------------------
>> >> Petitboeuf's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=10602
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=535963
>> >>
>> >>
>>
>>
>>

Laurina · 04-26-2006, 05:15 AM

thanks. Have done this but #ref doesn't go away. possibly something to do
with the server??

"Roger Govier" wrote:

> Hi Laurina
>
> I should have added that you need the Analysis Toolpak loaded.
> Tools>Addins> and check Analysis Toolpak
>
> --
> Regards
>
> Roger Govier
>
>
> "Laurina" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for that but the file isn't recognising the workday bit - comes
> > up
> > with #name and then #ref.
> >
> > "Roger Govier" wrote:
> >
> >> Hi Laurina
> >>
> >> Try using the Workday() function.
> >>
> >> With your first date in A1, in A2 enter
> >> =WORKDAY(A1,1)
> >> Copy down and you will just get the workdays of each week.
> >> If you want to exclude Public Holidays from the list, then pout those
> >> dates in a range of cells and either name the range as Holidays or
> >> refer
> >> directly to the range of cells holding the dates with the following
> >> modified formula
> >> =WORKDAY(A1,1,holidays) or = WORKDAY(A1,1,$E1:$E10) where E1:E10
> >> holds
> >> the range of holiday dates.
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Laurina" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Not quite. Here's an example
> >> >
> >> > M 28-Sep
> >> > T 29-Sep
> >> > W 30-Sep
> >> > Th 1-Oct
> >> > F 2-Oct
> >> > M 4-Oct
> >> > T 5-Oct
> >> > W 6-Oct
> >> > Th 7-Oct
> >> > F 8-Oct
> >> >
> >> >
> >> > "Petitboeuf" wrote:
> >> >
> >> >>
> >> >> Laurina
> >> >>
> >> >> I am using Excel 2003 and I have tried the following:
> >> >>
> >> >> Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday
> >> >> Thursday Friday
> >> >>
> >> >> in columns A to J.
> >> >>
> >> >> When i select the lot then drag to the right it starts from Monday
> >> >> then
> >> >> end the week on Friday, then starts again with Monday, etc...
> >> >>
> >> >> Is this what you are after?
> >> >>
> >> >>
> >> >> --
> >> >> Petitboeuf
> >> >> ------------------------------------------------------------------------
> >> >> Petitboeuf's Profile:
> >> >> http://www.excelforum.com/member.php...o&userid=10602
> >> >> View this thread:
> >> >> http://www.excelforum.com/showthread...hreadid=535963
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Laurina · 04-26-2006, 05:20 AM

ignore last message - had entered date as text - it now works - thanks

"Roger Govier" wrote:

> Hi Laurina
>
> I should have added that you need the Analysis Toolpak loaded.
> Tools>Addins> and check Analysis Toolpak
>
> --
> Regards
>
> Roger Govier
>
>
> "Laurina" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for that but the file isn't recognising the workday bit - comes
> > up
> > with #name and then #ref.
> >
> > "Roger Govier" wrote:
> >
> >> Hi Laurina
> >>
> >> Try using the Workday() function.
> >>
> >> With your first date in A1, in A2 enter
> >> =WORKDAY(A1,1)
> >> Copy down and you will just get the workdays of each week.
> >> If you want to exclude Public Holidays from the list, then pout those
> >> dates in a range of cells and either name the range as Holidays or
> >> refer
> >> directly to the range of cells holding the dates with the following
> >> modified formula
> >> =WORKDAY(A1,1,holidays) or = WORKDAY(A1,1,$E1:$E10) where E1:E10
> >> holds
> >> the range of holiday dates.
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Laurina" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Not quite. Here's an example
> >> >
> >> > M 28-Sep
> >> > T 29-Sep
> >> > W 30-Sep
> >> > Th 1-Oct
> >> > F 2-Oct
> >> > M 4-Oct
> >> > T 5-Oct
> >> > W 6-Oct
> >> > Th 7-Oct
> >> > F 8-Oct
> >> >
> >> >
> >> > "Petitboeuf" wrote:
> >> >
> >> >>
> >> >> Laurina
> >> >>
> >> >> I am using Excel 2003 and I have tried the following:
> >> >>
> >> >> Monday Tuesday Wednesday Thursday Friday Monday Tuesday Wednesday
> >> >> Thursday Friday
> >> >>
> >> >> in columns A to J.
> >> >>
> >> >> When i select the lot then drag to the right it starts from Monday
> >> >> then
> >> >> end the week on Friday, then starts again with Monday, etc...
> >> >>
> >> >> Is this what you are after?
> >> >>
> >> >>
> >> >> --
> >> >> Petitboeuf
> >> >> ------------------------------------------------------------------------
> >> >> Petitboeuf's Profile:
> >> >> http://www.excelforum.com/member.php...o&userid=10602
> >> >> View this thread:
> >> >> http://www.excelforum.com/showthread...hreadid=535963
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>