days and dates

[Guest]

hi
I am making a new room booking form and I want it to show the day and date
ie. "Monday 1 January 2007". How do I make a formula that will include the
day as well as the date, and omit weekends?
Thanks
msiz

 

[Dave Peterson]

Put
01/01/2007
in the top cell (A1)
Format|Cells|Number tab|Custom category
dddd d mmmm yyyy

And select that cell
rightclick on the autofill button (the little square in the bottom right corner
of the selected cell)
and drag down as far as you want.

When you release the mouse, you can choose from a bunch of
possibilities--including "Fill Weekdays"

 

[Phil]

Hi,

Not sure if this is what you want, but ....

Format the cell using custom, and in the panel type dddd dd mmmm yyyy

That will show a date as Monday 01 January 2007

To copy this as a series, right-click on the autofill handle (the black
dot in the bottom right of the cell) then drag down. When you stop
choose "Fill Weekdays" and the weekends will be omitted from the list.

I note you asked for a formula, so what I suggest may not work for
you....on the other hand it might remove the need for a formula!

Hope this helps

Phil

 

[Guest]

thanks
sorry should have been clearer - I will need a new page for every day of the
week
msiz

 

[Dave Peterson]

Does this mean a new printed page on that single worksheet?

Or does it mean you want a different worksheet for each of those dates? If you
mean this, where does the date go--is it the name of the worksheet or is it a
cell in the worksheet?

 

[Guest]

hi

yes I need a page for each weekday in the same sheet

 

[Dave Peterson]

Add the weekdays to the worksheet (as many as you need).

Then run this macro:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets(1)
.ResetAllPageBreaks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
.HPageBreaks.Add Before:=.Cells(iRow, "A")
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

hi

yes I need a page for each weekday in the same sheet