Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
days and dates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
days and dates
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" msiz wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
days and dates
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 msiz wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
days and dates
thanks
sorry should have been clearer - I will need a new page for every day of the week msiz "Dave Peterson" wrote: 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" msiz wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
days and dates
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? msiz wrote: thanks sorry should have been clearer - I will need a new page for every day of the week msiz "Dave Peterson" wrote: 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" msiz wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
days and dates
hi
yes I need a page for each weekday in the same sheet "Dave Peterson" wrote: 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? msiz wrote: thanks sorry should have been clearer - I will need a new page for every day of the week msiz "Dave Peterson" wrote: 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" msiz wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
days and dates
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 Befo=.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 msiz wrote: hi yes I need a page for each weekday in the same sheet "Dave Peterson" wrote: 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? msiz wrote: thanks sorry should have been clearer - I will need a new page for every day of the week msiz "Dave Peterson" wrote: 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" msiz wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Number of Months Weeks and Days Between Two Dates | Excel Worksheet Functions | |||
adding only working days to dates | Excel Discussion (Misc queries) | |||
challenging formula(for me), counting days between dates for multipleyears | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Brainteaser about Days Between Dates | Excel Discussion (Misc queries) |