Thread
:
Calculating time from a specific paycheck period
View Single Post
#
4
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
Posts: 2,345
Calculating time from a specific paycheck period
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(A 10)+$B$2-2,14*(B2=4)+1))=1))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"BobR" wrote in message
...
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" wrote in message
...
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
Replace @mailinator.com with @tiscali.co.uk
"BobR" wrote in message
.. .
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 With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann