Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

Use the formula =A2 (assuming that the first date is in A2) and then format
the cell as Custom "ddd" (without the quotes)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dean" wrote in message
...
Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on
a
formula for creating col 1. A table on another sheet is OK and no problem
re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

On 31 Jul, 22:52, Dean wrote:
Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

Dean,

Set A1 (or any other to the beginning of the period of time you want
ie 08/01/07,

in A2 place =WORKDAY($A$1,B2,0)
in B2 place 1, B3 2, B4 3 ...etc

Copy A2 and B2 down as far as you like and you will get the dates you
need

As for getting the days you seem ok, if not you need a separate table
1 to 7 and perform a vlookup 1=sunday 7=saturday

Matthew
..

If the start date is in cell A1, put this formula in cell A2 and drag down:

=IF(WEEKDAY(A1)<6,A1+1,A1+3)

in cell B1, put the formula =A1 and format as "ddd" and drag down

Regards

Trevor


"Dean" wrote in message
...
Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on
a
formula for creating col 1. A table on another sheet is OK and no problem
re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

Assume you put your start date in A1 (not a weekend day) - enter this
in A2:

=IF(WEEKDAY(A1,2)=5,A1+3,A1+1)

and copy down. You can save yourself a column by applying a custom
format to column A as "ddd, m/d/yy" (without the quotes).

Hope this helps.

Pete

On Jul 31, 10:52 pm, Dean wrote:
Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

I'm not explaining well. I want the values (dates) in column A to be drawn
in based on a formula that would in essence say
if today is not a weekend day make this cell the next day after the cell
above me
that is not a weekend day.
This can be a lookup based on a table in another workseet if necessary.

Thanks

"Dean" wrote:

Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

On 31 Jul, 23:29, Pete_UK wrote:
Assume you put your start date in A1 (not a weekend day) - enter this
in A2:

=IF(WEEKDAY(A1,2)=5,A1+3,A1+1)

and copy down. You can save yourself a column by applying a custom
format to column A as "ddd, m/d/yy" (without the quotes).

Hope this helps.

Pete

On Jul 31, 10:52 pm, Dean wrote:

Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

I think you may have missed the OP point, he can sort out Col 2

can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1

Thus the above may not do what the OP wants, or am I being thick ?

Matthew

On 31 Jul, 23:39, Matthew wrote:
On 31 Jul, 23:29, Pete_UK wrote:



Assume you put your start date in A1 (not a weekend day) - enter this
in A2:

=IF(WEEKDAY(A1,2)=5,A1+3,A1+1)

and copy down. You can save yourself a column by applying a custom
format to column A as "ddd, m/d/yy" (without the quotes).

Hope this helps.

Pete

On Jul 31, 10:52 pm, Dean wrote:

Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

I think you may have missed the OP point, he can sort out Col 2

can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1

Thus the above may not do what the OP wants, or am I being thick ?

Matthew

Dean

If you use my formula it will work the others will not.

Matthew

My formula will give him the dates he wants in column 1, which is what
he asked for.

I also suggested that he might not need column 2, as he can just
format column 1 appropriately.

Your approach suggested that he put some numbers in column B, which is
presumably where he wants to derive his days, so how will that work?

Pete

On Jul 31, 11:39 pm, Matthew wrote:
On 31 Jul, 23:29, Pete_UK wrote:





Assume you put your start date in A1 (not a weekend day) - enter this
in A2:

=IF(WEEKDAY(A1,2)=5,A1+3,A1+1)

and copy down. You can save yourself a column by applying a custom
format to column A as "ddd, m/d/yy" (without the quotes).

Hope this helps.

Pete

On Jul 31, 10:52 pm, Dean wrote:

Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

I think you may have missed the OP point, he can sort out Col 2

can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1

Thus the above may not do what the OP wants, or am I being thick ?

Matthew- Hide quoted text -

- Show quoted text -

Well Pete's formula definitely works and it doesn't use neither a help
column nor a function from the ATP but in this case there is no real need
for a formula, if you put 8/1/07 in A1 then just copy it down using right
button of the mouse and when you let go of the button you can select fill
weekdays only.



--
Regards,

Peo Sjoblom



"Matthew" wrote in message
ups.com...
On 31 Jul, 23:39, Matthew wrote:
On 31 Jul, 23:29, Pete_UK wrote:



Assume you put your start date in A1 (not a weekend day) - enter this
in A2:

=IF(WEEKDAY(A1,2)=5,A1+3,A1+1)

and copy down. You can save yourself a column by applying a custom
format to column A as "ddd, m/d/yy" (without the quotes).

Hope this helps.

Pete

On Jul 31, 10:52 pm, Dean wrote:

Am using Excel 2007 and I want to create a list of days of the week
for a
month as below I can convert the col 2 info based on Col 1. Any
idea on a
formula for creating col 1. A table on another sheet is OK and no
problem re
holidays. I just don't want weekend days shown and can't quite
noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

I think you may have missed the OP point, he can sort out Col 2

can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1

Thus the above may not do what the OP wants, or am I being thick ?

Matthew

Dean

If you use my formula it will work the others will not.

Matthew

On 31 Jul, 23:53, "Peo Sjoblom" wrote:
Well Pete's formula definitely works and it doesn't use neither a help
column nor a function from the ATP but in this case there is no real need
for a formula, if you put 8/1/07 in A1 then just copy it down using right
button of the mouse and when you let go of the button you can select fill
weekdays only.

--
Regards,

Peo Sjoblom

"Matthew" wrote in message

ups.com...

On 31 Jul, 23:39, Matthew wrote:
On 31 Jul, 23:29, Pete_UK wrote:

Assume you put your start date in A1 (not a weekend day) - enter this
in A2:

=IF(WEEKDAY(A1,2)=5,A1+3,A1+1)

and copy down. You can save yourself a column by applying a custom
format to column A as "ddd, m/d/yy" (without the quotes).

Hope this helps.

Pete

On Jul 31, 10:52 pm, Dean wrote:

Am using Excel 2007 and I want to create a list of days of the week
for a
month as below I can convert the col 2 info based on Col 1. Any
idea on a
formula for creating col 1. A table on another sheet is OK and no
problem re
holidays. I just don't want weekend days shown and can't quite
noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

I think you may have missed the OP point, he can sort out Col 2

can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1

Thus the above may not do what the OP wants, or am I being thick ?

Matthew

Dean

If you use my formula it will work the others will not.

Matthew

Peo

Well crikey, it may be school boy stuff for you but what a
discovery....right button.... I should have read those books
better...A dream solution, that to be honest I will end up using I
feel such a prat!


Pete....still don't see yours working am I being overly thick ?


Matthew

Matthew,

I suggest you try it and see.

Pete

On Aug 1, 12:09 am, Matthew wrote:


Pete....still don't see yours working am I being overly thick ?

What determines which month you want to use for this? You can calculate the
first weekday of the month and then use that date to calculate the rest of
the weekdays for that month. Totally dynamic. Do you want it for the current
month? This means that on the first of the next month it'll automatically
recalulate for the new month.

--
Biff
Microsoft Excel MVP


"Dean" wrote in message
...
I'm not explaining well. I want the values (dates) in column A to be
drawn
in based on a formula that would in essence say
if today is not a weekend day make this cell the next day after the cell
above me
that is not a weekend day.
This can be a lookup based on a table in another workseet if necessary.

Thanks

"Dean" wrote:

Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea
on a
formula for creating col 1. A table on another sheet is OK and no
problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

I would like to thank all of the responders. Perhaps if I got some sleep I
could have figured this out without your help, but I doubt it. Anyway thanks.
Peo, what an elegant discovery. It's truly wonderful. Thank you thank you.

Dean

"Dean" wrote:

Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on a
formula for creating col 1. A table on another sheet is OK and no problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

On 1 Aug, 01:33, Pete_UK wrote:
Matthew,

I suggest you try it and see.

Pete

On Aug 1, 12:09 am, Matthew wrote:



Pete....still don't see yours working am I being overly thick ?

Pete,

A Thousand apologies ! I could read the instruction and kept putting
it in B2 ..... What a Fool

Matthew

Another option if you do not like dragging down a large number of rows in
Excel 2007 might be to place a valid date in A1. Select A1.
Home | Editing | Fill | Series... (Peo's suggestion)
Select Columns, Date, Weekday, Step value =1.
For the Stop Value, the technique here is that you can enter a valid date.
For example, set your stop value to 12/31/2008, and Excel will fill the
sheet for you.

Just another option for an equation might be:

=A1+MOD(241,WEEKDAY(A1)+1)

--
Dana DeLouis


"Dean" wrote in message
...
Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea on
a
formula for creating col 1. A table on another sheet is OK and no problem
re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue

Dana,

=A1+MOD(241,WEEKDAY(A1)+1)


It amazes me how hou keep pulling these numbers out of the air!

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

=A1+MOD(241,WEEKDAY(A1)+1)

What's the significance of 241?

--
Biff
Microsoft Excel MVP


"Dana DeLouis" wrote in message
...
Another option if you do not like dragging down a large number of rows in
Excel 2007 might be to place a valid date in A1. Select A1.
Home | Editing | Fill | Series... (Peo's suggestion)
Select Columns, Date, Weekday, Step value =1.
For the Stop Value, the technique here is that you can enter a valid date.
For example, set your stop value to 12/31/2008, and Excel will fill the
sheet for you.

Just another option for an equation might be:

=A1+MOD(241,WEEKDAY(A1)+1)

--
Dana DeLouis


"Dean" wrote in message
...
Am using Excel 2007 and I want to create a list of days of the week for a
month as below I can convert the col 2 info based on Col 1. Any idea
on a
formula for creating col 1. A table on another sheet is OK and no
problem re
holidays. I just don't want weekend days shown and can't quite noodle it
out.
Col 1 Col2
8/1/07 Wed
8/2/07 Thu
8/3/07 Fri
8/6/07 Mon
8/7/07 Tue