Need semi-month date function

  • Thread starter Thread starter Will Fleenor
  • Start date Start date
W

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

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

Regards,

Daniel M.
 
Will Fleenor said:
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?
Click to expand...

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)
 
Back
Top