function to fill all days of month to end of month

Y

YaHootie

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

Richard Buttrey

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.
Click to expand...


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
__________________________
 
C

Cutter

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
 
Y

YaHootie

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?
 
Y

YaHootie

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?
 
Y

YaHootie

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

Thank You Very Much.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
M

Myrna Larson

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

Cutter

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

Monique

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

Monique
 
B

Biff

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
 
Top