Filling in specific dates in Excel
 

I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Filling in specific dates in Excel
 

Type in 1/1/07 in B1 and 15/1/07 (or 1/15/07) in C1, then in D1 enter
this formula:

=DATE(YEAR(B1),MONTH(B1)+1,DAY(B1))

Then copy this across row 1 for as many columns as you need.

You can have different start dates in B1:C1.

Hope this helps.

Pete

On Nov 18, 6:28 pm, wrote:
I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Filling in specific dates in Excel
 

On Sun, 18 Nov 2007 10:28:42 -0800 (PST), wrote:

I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Instead of auto-fill, why not use a formula:

=IF(DAY(A1)=15,A1+33-DAY(A1+32),A1+14)
--ron

Filling in specific dates in Excel
 

In A1 : 1/1/2007
In A2 : 1/15/2007

Select both cells and copy down with your mouse but not with your right mouse button
but with your left button.
When you release your mouse you see a dialog with the option "Fill months"




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message ...
I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Filling in specific dates in Excel
 

Oops

It is Left - right

filldown with your right button


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
In A1 : 1/1/2007
In A2 : 1/15/2007

Select both cells and copy down with your mouse but not with your right mouse button
but with your left button.
When you release your mouse you see a dialog with the option "Fill months"




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message ...
I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Filling in specific dates in Excel
 

Try this:

Enter your first date in cell A1.

Enter this formula in B1 and copy across as needed:

=IF(DAY(A1)=1,A1+14,A1+17-DAY(A1+17)+1)

Format the cells as DATE

--
Biff
Microsoft Excel MVP


wrote in message
...
I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Filling in specific dates in Excel
 

Hi,

Another way:

with a starting date in A1, in B1 put:
=DATE(YEAR(A1),IF(DAY(A1)=1,MONTH(A1),MONTH(A1)+1) ,IF(DAY(A1)=1,15,1))
copy down

HTH
Jean-Guy

" wrote:

I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Filling in specific dates in Excel
 

If you are not worried about work vs nonwork dates.
B1: =IF(DAY(A1)=1,A1+14,IF(DAY(A1)=15,DATE(YEAR(A1),MO NTH(A1)+1,1),"broken"))

Format the row as a date,
fill across with fill handle, and will continue into the next year(s).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


wrote in message ...
I receive income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Filling in specific dates in Excel
 

On Sun, 18 Nov 2007 13:50:06 -0500, Ron Rosenfeld
wrote:

On Sun, 18 Nov 2007 10:28:42 -0800 (PST), wrote:

I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Instead of auto-fill, why not use a formula:

=IF(DAY(A1)=15,A1+33-DAY(A1+32),A1+14)
--ron

This assumes A1 contains a date that is either the 1st or 15th of some month.
--ron

Filling in specific dates in Excel
 

Good one Ron


Gord

On Sun, 18 Nov 2007 19:55:14 +0100, "Ron de Bruin"
wrote:

In A1 : 1/1/2007
In A2 : 1/15/2007

Select both cells and copy down with your mouse but not with your right mouse button
but with your left button.
When you release your mouse you see a dialog with the option "Fill months"

Filling in specific dates in Excel
 

Good one Ron

Thanks Gord

Not many people know about this trick

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
Good one Ron


Gord

On Sun, 18 Nov 2007 19:55:14 +0100, "Ron de Bruin"
wrote:

In A1 : 1/1/2007
In A2 : 1/15/2007

Select both cells and copy down with your mouse but not with your right mouse button
but with your left button.
When you release your mouse you see a dialog with the option "Fill months"

Filling in specific dates in Excel
 

On Nov 18, 1:49 pm, Pete_UK wrote:
Type in 1/1/07 in B1 and 15/1/07 (or 1/15/07) in C1, then in D1 enter
this formula:

=DATE(YEAR(B1),MONTH(B1)+1,DAY(B1))

Then copy this across row 1 for as many columns as you need.

You can have different start dates in B1:C1.

Hope this helps.

Pete

On Nov 18, 6:28 pm, wrote:



I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?- Hide quoted text -

- Show quoted text -

I got a #NUM error on this one.

Sorry.

Filling in specific dates in Excel
 

On Nov 18, 1:50 pm, Ron Rosenfeld wrote:
On Sun, 18 Nov 2007 10:28:42 -0800 (PST), wrote:
I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?

Instead of auto-fill, why not use a formula:

=IF(DAY(A1)=15,A1+33-DAY(A1+32),A1+14)
--ron

This worked perfectly for me.

Thank-you

Filling in specific dates in Excel
 

On Nov 18, 1:55 pm, "Ron de Bruin" wrote:
In A1 : 1/1/2007
In A2 : 1/15/2007

Select both cells and copy down with your mouse but not with your right mouse button
but with your left button.
When you release your mouse you see a dialog with the option "Fill months"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



wrote in ...
I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?- Hide quoted text -

- Show quoted text -

This was JUST simply beautiful What a fantastic shortcut. Thank you
so much...

Filling in specific dates in Excel
 

On Nov 18, 1:59 pm, "T. Valko" wrote:
Try this:

Enter your first date in cell A1.

Enter this formula in B1 and copy across as needed:

=IF(DAY(A1)=1,A1+14,A1+17-DAY(A1+17)+1)

Format the cells as DATE

--
Biff
Microsoft Excel MVP

wrote in message

...



I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?- Hide quoted text -

- Show quoted text -

Thank you. this seems to work similar to the earlier post, using 17
instead of 34/32 This works for me as well. Thanks.

Filling in specific dates in Excel
 

So many answers, and not all within minutes, was a server down.

Filling in specific dates in Excel
 

wrote in message
...
On Nov 18, 1:59 pm, "T. Valko" wrote:
Try this:

Enter your first date in cell A1.

Enter this formula in B1 and copy across as needed:

=IF(DAY(A1)=1,A1+14,A1+17-DAY(A1+17)+1)

Format the cells as DATE

--
Biff
Microsoft Excel MVP

wrote in message

...



I recieve income on the 1st and the 15th of every month, regardless of
the month, or day.

Understanding the serial number feature of Excel, I cannot create a
autofill that will fill in the 1st and 15th of every month.

I need to be able to type in the 1st of a month then autofill across
and have it pick up the 1st and 15th of every month.

How can this be performed?- Hide quoted text -

- Show quoted text -

Thank you. this seems to work similar to the earlier post, using 17
instead of 34/32 This works for me as well. Thanks.


You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Filling in specific dates in Excel
 

Worked okay for me - did you type the formula incorrectly?

Pete

On Nov 19, 1:09 am, wrote:

I got a #NUM error on this one.

Sorry