Calculating time from a specific paycheck period

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BobR - 10 Sep 2007 22:28 GMT

Hello
I have excel 2003 installed and am looking for a solution to this problem

I have pay periods of Weekly BiWeekly Bi Monthly and Monthly in cell
A2 and they are selected by a list by data validation with a pull down.

I have in A10 a date that the payroll first begins for this person
A11 and on down are being listed as the next payday.

My question is:
A11 and down I want to have a formula that will say ifA2=biweekly, a10+14,
ifA2=weekly,A10+7 but I don't know how to make the formula in the if
statemnt if A2 = bi monthly or monthly and have it come out exactly in the
middle of the month or a t the beginning of the month.

If bimonthly then should select 15 or thereabouts if monthly jan, feb,
march on the first.

Any help would be appreciated
Thanks
BOB

Reply to this Message

Sandy Mann - 10 Sep 2007 23:25 GMT

See If this works for you.

With the list for Data Validation in H1:H4, enter in B2:
=MATCH(A2,H1:H4,0)

With the pay period (presumably a Monday), in A10 enter in A11 and copy down
the formula:
=IF($B$2<3,A10-WEEKDAY(A10,2)-2+$B$2*7,DATE(YEAR(A10),MONTH(A10)+$B$2-2,15)-WEEKDAY(DATE(YEAR(A10),MONTH(A10)+$B$2-2,15),2)+5)

This should return the following Friday for weekly or the Friday after that
for bi-weekly and the Friday nearest to the 15th of the month for monthly
and bi-monthly.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hello
> I have excel 2003 installed and am looking for a solution to this problem
[quoted text clipped - 18 lines]
> Thanks
> BOB

Reply to this Message

BobR - 23 Sep 2007 13:59 GMT

Sandy,

Sorry I took so long in getting back, computer issues...
Works great for Weekly and Bi-Weekly
Is there a way for the Bi-Monthly and Monthly to not select a friday and
merely return a data that is in the middle of the month for Bi-Montly or the
first of the month for Monthly?? The only two paydays on Friday would be
the Weekly and Bi-weekly.

Thanks so much in advance.

Bob

> See If this works for you.
>
[quoted text clipped - 31 lines]
>> Thanks
>> BOB

Reply to this Message

Sandy Mann - 23 Sep 2007 14:51 GMT

Hi Bob,

If by the middle of the month you mean the 15th then try:

=IF($B$2<3,A10-WEEKDAY(A10,2)-2+$B$2*7,DATE(YEAR(A10),MONTH(A10)+$B$2-2,14*(B2=4)+1))

The 15th may fall on a weekend so if you want a Saturday to show the
previous Friday and Sinday to show the following Monday try:

=IF($B$2<3,A10-WEEKDAY(A10,2)-2+$B$2*7,DATE(YEAR(A10),MONTH(A10)+$B$2-2,14*(B2=4)+1)-(WEEKDAY(DATE(YEAR(A10),MONTH(A10)+$B$2-2,14*(B2=4)+1))=7)+(WEEKDAY(DATE(YEAR(A10),MONTH(A10)+$B$2-2,14*(B2=4)+1))=1))

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Sandy,
>
[quoted text clipped - 45 lines]
>>> Thanks
>>> BOB

Reply to this Message