Need semi-month date function

[Will Fleenor]

I often need to fill a series of dates with semi-monthly dates. I have
tried putting in one month (15th and 30th or 31st) and filling in below but
Excel adds the number of intervening days (i.e. 15 or 16 as the increment).
Is there a function or formula I can use?

Thanks Will
(e-mail address removed)

 

[JohnI]

Will,

try this formula-

=IF(DAY(A2)>27,DATE(YEAR(A2),MONTH(A2)+1,DAY(DATE(YEAR(A2),MONTH(A2)+2,0))/2
),DATE(YEAR(A2),MONTH(A2)+1,0))

& put your first date in A2 e.g. 1/1/2003

regards,

JohnI

 

[Bob Phillips]

Will,

Put your first date in A1, and the following formula in A2
if A1 is the 15th, then =DATE(YEAR(A1),MONTH(A1)+1,(MOD(ROW(), 2)=1)*15)
if A1 is 30/31st, then =DATE(YEAR(A1),MONTH(A1)+1,(MOD(ROW(), 2)=0)*15))

 

[Bradley Dawson]

type "1/15" press enter then type "2/15" below the previous entry. Now
fill these out to the month/year you want.
Below that list, type "1/31", and "2/28" below it and fill as above.
Highlight both lists and sort using Data>Sort or the icon on the toolbar
with an A over a Z and an arrow pointing down.

 

[Daniel.M]

Hi,

* enter a start date, say 15 jan 2003, in A1
* enter in A2:
=IF(DAY(A1)=15,DATE(YEAR(A1),MONTH(A1)+1,0),DATE(YEAR(A1),MONTH(A1)+1,15))
* fill down

or in A2:
=DATE(YEAR(A1),MONTH(A1)+1,15*(DAY(A1)<>15))

Regards,

Daniel M.

 

[Anders S]

Very nice, Daniel

Anders

Hi,


or in A2:
=DATE(YEAR(A1),MONTH(A1)+1,15*(DAY(A1)<>15))

Regards,

Daniel M.

 

[Harlan Grove]

I often need to fill a series of dates with semi-monthly dates. I have
tried putting in one month (15th and 30th or 31st) and filling in below but
Excel adds the number of intervening days (i.e. 15 or 16 as the increment).
Is there a function or formula I can use?

If you start with a 15th or final date in a month in cell A1, enter this
formula in A2 and fill down as far as needed.

=IF(DAY(A1)=15,A1+17-DAY(A1+17),A1+15)