Filling in specific dates in Excel

[urandrod]

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?

 

[Pete_UK]

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

 

[Ron Rosenfeld]

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

 

[Ron de Bruin]

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"

 

[Ron de Bruin]

Oops

It is Left - right

filldown with your right button

 

[T. Valko]

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

 

[Guest]

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

 

[David McRitchie]

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

Format the row as a date,
fill across with fill handle, and will continue into the next year(s).

 

[Ron Rosenfeld]

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

 

[Gord Dibben]

Good one Ron


Gord

 

[Ron de Bruin]

Good one Ron

Thanks Gord

Not many people know about this trick

 

[urandrod]

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






- Show quoted text -

I got a #NUM error on this one.

Sorry.

 

[urandrod]

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

 

[urandrod]

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








- Show quoted text -

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

 

[urandrod]

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









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

 

[David McRitchie]

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

 

[T. Valko]

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!

 

[Pete_UK]

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

Pete