|
Calculating time from a specific paycheck period
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 |
Calculating time from a specific paycheck period
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 |
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 |
| All times are GMT +1. The time now is 11:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com