Date Calculation

[WLMPilot]

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

 

[T. Valko]

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

 

[Niek Otten]

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),DATE(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

|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

 

[T. Valko]

Ooops!

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

 

[WLMPilot]

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

Thanks,
Les