function to fill all days of month to end of month
 

I need help on a function in a spreadsheet that will list all of the days in
a given month automaticaly with the entry of the 1st of the month only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even for
shorted months such as Feb.

Thank you in advance.

On Sun, 2 Oct 2005 15:25:02 -0700, "YaHootie"
wrote:

I need help on a function in a spreadsheet that will list all of the days in
a given month automaticaly with the entry of the 1st of the month only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even for
shorted months such as Feb.

Thank you in advance.


Enter 10/01/05 in A1and 11/01/05 in A2

(The UK interpretation of these dates are 10th and 11th January 2005,
although presumably it doesn't matter if you're posting from the US
and will be entering 10/01/05 and 10/02/05)

Select both dates then click and drag the small square handle on A2
down as far as necessary.

HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Assuming your entered date is in A1

In A2 type this formula:

=IF(A1="","",IF(MONTH(A1+1)=MONTH(A$1),A1+1,""))

and drag fill the formula down to A31


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=472473

Thank you Richard, I'm presently using the drag method now but was looking
for a way to have Excel automate this process - lazy you know. Do you or
anyone know of a way to preform this operation automaticaly?

"Richard Buttrey" wrote:

On Sun, 2 Oct 2005 15:25:02 -0700, "YaHootie"
wrote:

I need help on a function in a spreadsheet that will list all of the days in
a given month automaticaly with the entry of the 1st of the month only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even for
shorted months such as Feb.

Thank you in advance.


Enter 10/01/05 in A1and 11/01/05 in A2

(The UK interpretation of these dates are 10th and 11th January 2005,
although presumably it doesn't matter if you're posting from the US
and will be entering 10/01/05 and 10/02/05)

Select both dates then click and drag the small square handle on A2
down as far as necessary.

HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Thank you Cutter, you have me on a good path, your formula does basically
what I want it to do. Is there a way to automate this formula to enter
monthly days by entering only the first day in cell A1 and having the rest of
the dates entered automatically?

"Cutter" wrote:


Assuming your entered date is in A1

In A2 type this formula:

=IF(A1="","",IF(MONTH(A1+1)=MONTH(A$1),A1+1,""))

and drag fill the formula down to A31


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=472473

Thank you Cutter, I responed to quickly. Your formula works exactly as I
want it to do.

Thank You Very Much.

"Cutter" wrote:


Assuming your entered date is in A1

In A2 type this formula:

=IF(A1="","",IF(MONTH(A1+1)=MONTH(A$1),A1+1,""))

and drag fill the formula down to A31


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=472473

On Sun, 2 Oct 2005 15:25:02 -0700, "YaHootie"
wrote:

I need help on a function in a spreadsheet that will list all of the days in
a given month automaticaly with the entry of the 1st of the month only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even for
shorted months such as Feb.

Thank you in advance.

A2: =(A1+1)*(MONTH(A1+1)=MONTH($A$1))

Copy/Drag down to A31

Then Format/Cells/Number/Custom Type: mm/dd/yy;;

(Note the two semicolons at the end).


--ron

Assuming you will type the first date in cell A1, in A2 put this formula

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))

and copy it down through A31. You only have to do this once. After that,
changing the value in A1 will fill in the remaining dates. If a given day
doesn't occur in that month the cell will appear to be empty.

On Sun, 2 Oct 2005 16:59:02 -0700, "YaHootie"
wrote:

Thank you Richard, I'm presently using the drag method now but was looking
for a way to have Excel automate this process - lazy you know. Do you or
anyone know of a way to preform this operation automaticaly?

"Richard Buttrey" wrote:

On Sun, 2 Oct 2005 15:25:02 -0700, "YaHootie"
wrote:

I need help on a function in a spreadsheet that will list all of the days
in
a given month automaticaly with the entry of the 1st of the month only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even
for
shorted months such as Feb.

Thank you in advance.


Enter 10/01/05 in A1and 11/01/05 in A2

(The UK interpretation of these dates are 10th and 11th January 2005,
although presumably it doesn't matter if you're posting from the US
and will be entering 10/01/05 and 10/02/05)

Select both dates then click and drag the small square handle on A2
down as far as necessary.

HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Yahootie, you're welcome. You had me scratching my head after your
first message to me but then I read your second one.

Glad it worked for you.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=472473

function to fill all days of month to end of month
 

I would like to know if there is a way to use the workday function with this
formula.

Monique

"Myrna Larson" wrote:

Assuming you will type the first date in cell A1, in A2 put this formula

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))

and copy it down through A31. You only have to do this once. After that,
changing the value in A1 will fill in the remaining dates. If a given day
doesn't occur in that month the cell will appear to be empty.

On Sun, 2 Oct 2005 16:59:02 -0700, "YaHootie"
wrote:

Thank you Richard, I'm presently using the drag method now but was looking
for a way to have Excel automate this process - lazy you know. Do you or
anyone know of a way to preform this operation automaticaly?

"Richard Buttrey" wrote:

On Sun, 2 Oct 2005 15:25:02 -0700, "YaHootie"
wrote:

I need help on a function in a spreadsheet that will list all of the days
in
a given month automaticaly with the entry of the 1st of the month only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month even
for
shorted months such as Feb.

Thank you in advance.


Enter 10/01/05 in A1and 11/01/05 in A2

(The UK interpretation of these dates are 10th and 11th January 2005,
although presumably it doesn't matter if you're posting from the US
and will be entering 10/01/05 and 10/02/05)

Select both dates then click and drag the small square handle on A2
down as far as necessary.

HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

function to fill all days of month to end of month
 

Hi!

To make it easier and the formula shorter (not wanting to hardcode a date in
the formula), enter a date for the month you're interested in:

A1 = 5/1/2006
H1:H10 = list of holiday dates to be excluded, if any.

Formula:

=IF(MONTH(A$1)=MONTH(WORKDAY(A$1-DAY(A$1),ROWS($1:1),H$1:H$10)),WORKDAY(A$1-DAY(A$1),ROWS($1:1),H$1:H$10),"")

Copy down until you get blanks.

Biff

"Monique" wrote in message
...
I would like to know if there is a way to use the workday function with
this
formula.

Monique

"Myrna Larson" wrote:

Assuming you will type the first date in cell A1, in A2 put this formula

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))

and copy it down through A31. You only have to do this once. After that,
changing the value in A1 will fill in the remaining dates. If a given day
doesn't occur in that month the cell will appear to be empty.

On Sun, 2 Oct 2005 16:59:02 -0700, "YaHootie"
wrote:

Thank you Richard, I'm presently using the drag method now but was
looking
for a way to have Excel automate this process - lazy you know. Do you
or
anyone know of a way to preform this operation automaticaly?

"Richard Buttrey" wrote:

On Sun, 2 Oct 2005 15:25:02 -0700, "YaHootie"
wrote:

I need help on a function in a spreadsheet that will list all of the
days
in
a given month automaticaly with the entry of the 1st of the month
only.

Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill

I would like the function to stop filling dates at end of the month
even
for
shorted months such as Feb.

Thank you in advance.


Enter 10/01/05 in A1and 11/01/05 in A2

(The UK interpretation of these dates are 10th and 11th January 2005,
although presumably it doesn't matter if you're posting from the US
and will be entering 10/01/05 and 10/02/05)

Select both dates then click and drag the small square handle on A2
down as far as necessary.

HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________