Calculating Social Security Payment Dates

[Guest]

I was wondering if anyone has any ideas as to how to calculate payment dates
for Social Security checks with Excel 2003. As an example: Social Security
makes its payments on a particular day (i.e. Wednesday) and week (i.e. 3rd
week) of every month. I've tried most of Date Functions, but the variance is
stumping me.

 

[Dave O]

Try this, as a starting point: in cells A1-A12, enter 1/1/2006,
2/1/2006 etc thru 12/1/2006. In B1 enter this formula, then copy and
paste down for the rest.
=DATE(YEAR(A1),MONTH(A1),5-WEEKDAY(A1)+14)

But check the April result: it indicates the first day of the month is
a Saturday, and on the Sunday-to-Saturday calendar that is technically
the first week (even though it's just one day)... so the Wednesday of
the third week, in that scenario, is April 12. If that doesn't do it,
replace this part of the formula above
5-WEEKDAY(A1)+14
....with an IF statement that says if the result is less than 12, or 15,
or some appropriate number, perform a different calculation.

 

[Sandy Mann]

Ron,

What constitutes the first week? A period of days with a Wednesday in it?
If so then you want the third Wednesday of the next month.

With any date in A1

=A1+33-DAY(A1+33)+1-WEEKDAY(A1+33-DAY(A1+33)+1-4)+21

should return the third Wednesday of the following month.

or moving the +#'s and -# 'sabout:

=A1-DAY(A1+33)-WEEKDAY(A1+33-DAY(A1+33)-3)+55
--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Ron Rosenfeld]

I was wondering if anyone has any ideas as to how to calculate payment dates
for Social Security checks with Excel 2003. As an example: Social Security
makes its payments on a particular day (i.e. Wednesday) and week (i.e. 3rd
week) of every month. I've tried most of Date Functions, but the variance is
stumping me.

Actually, Social Security makes its payments, according to my letter, on the
nth weekday of each month. That is subtly different.

Here's one way of doing it:

A1: 1 jan 2006
A2: =A1+33-DAY(A1+32)
Select A1:A2 & Format/Cells/Number/Custom Type: mmm yyyy

Select A2 and Copy/drag down as far as required

B1: =A1+7-WEEKDAY(A1-DAY(A1)+8-DOW)+(Num-1)*7

DOW = Day of Week (Sun=1, Mon=2, etc)
Num = 1, 2, 3, or 4 depending on which Monday, etc

Format/Cells/Number/Custom Type: dddd, mmmm dd, yyyy
or whatever.

Select B1 and copy/drag down as far as needed.

Column A will show month and year.
Column B will show the actual day/date
--ron