Weekdays of the month.

[Guest]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Matthew]

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
..

 

[Trevor Shuttleworth]

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

 

[Pete_UK]

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

 

[Guest]

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

 

[Matthew]

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

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

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

Matthew

 

[Matthew]

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


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

 

[Pete_UK]

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

 

[Peo Sjoblom]

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.

 

[Matthew]

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.

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

 

[Pete_UK]

Matthew,

I suggest you try it and see.

Pete

 

[T. Valko]

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.

 

[Guest]

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

 

[Matthew]

Matthew,

I suggest you try it and see.

Pete

Pete,

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

Matthew

 

[Dana DeLouis]

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)

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

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

What's the significance of 241?