Calculating time from a specific paycheck period

[BobR]

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

 

[Sandy Mann]

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.


--
HTH

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

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

 

[BobR]

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

 

[Sandy Mann]

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

--
HTH

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

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