I have a worksheet with columns that represent the dates that I get paid. I
get paid on the 15th and 30th of each month, unless, it is Feb, then the last
day of the month (28th or 29th).

I want to be able to have the date calculated automatically. I can figure
the calculation for the 30th (previous date + 15), but I don't know if there
is an easy way to calculate the 15th since the number of days in a month vary.

Any help greatly appreciated!!

Les

Try this:

Enter the first date in cell A1, either the 15th or last date for a
particular month: 1/15/2008 or 1/31/2008

Enter this formula in B1 and copy across as needed:

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

Format as DATE

--
Biff
Microsoft Excel MVP


"WLMPilot" wrote in message
...
I have a worksheet with columns that represent the dates that I get paid.
I
get paid on the 15th and 30th of each month, unless, it is Feb, then the
last
day of the month (28th or 29th).

I want to be able to have the date calculated automatically. I can figure
the calculation for the 30th (previous date + 15), but I don't know if
there
is an easy way to calculate the 15th since the number of days in a month
vary.

Any help greatly appreciated!!

Les

Hi Les,

With 15 Jan 2000 (or any other pay date) in A1, this formula in A2:

=IF(DAY(A1)=15,MIN(DATE(YEAR(A1),MONTH(A1),30),DAT E(YEAR(A1),MONTH(A1)+1,0)),DATE(YEAR(A1),MONTH(A1) +1,15))

and copy down as far as you need.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"WLMPilot" wrote in message ...
|I have a worksheet with columns that represent the dates that I get paid. I
| get paid on the 15th and 30th of each month, unless, it is Feb, then the last
| day of the month (28th or 29th).
|
| I want to be able to have the date calculated automatically. I can figure
| the calculation for the 30th (previous date + 15), but I don't know if there
| is an easy way to calculate the 15th since the number of days in a month vary.
|
| Any help greatly appreciated!!
|
| Les

Ooops!

Disregard. I see you wanted the 15th and 30th, not the eomonth!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

Enter the first date in cell A1, either the 15th or last date for a
particular month: 1/15/2008 or 1/31/2008

Enter this formula in B1 and copy across as needed:

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

Format as DATE

--
Biff
Microsoft Excel MVP


"WLMPilot" wrote in message
...
I have a worksheet with columns that represent the dates that I get paid.
I
get paid on the 15th and 30th of each month, unless, it is Feb, then the
last
day of the month (28th or 29th).

I want to be able to have the date calculated automatically. I can
figure
the calculation for the 30th (previous date + 15), but I don't know if
there
is an easy way to calculate the 15th since the number of days in a month
vary.

Any help greatly appreciated!!

Les

WOW, that worked great!!! Don't understand what the formula is doing, but
will try to break it down.

Thanks,
Les

"Niek Otten" wrote:

Hi Les,

With 15 Jan 2000 (or any other pay date) in A1, this formula in A2:

=IF(DAY(A1)=15,MIN(DATE(YEAR(A1),MONTH(A1),30),DAT E(YEAR(A1),MONTH(A1)+1,0)),DATE(YEAR(A1),MONTH(A1) +1,15))

and copy down as far as you need.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"WLMPilot" wrote in message ...
|I have a worksheet with columns that represent the dates that I get paid. I
| get paid on the 15th and 30th of each month, unless, it is Feb, then the last
| day of the month (28th or 29th).
|
| I want to be able to have the date calculated automatically. I can figure
| the calculation for the 30th (previous date + 15), but I don't know if there
| is an easy way to calculate the 15th since the number of days in a month vary.
|
| Any help greatly appreciated!!
|
| Les