Sso if you've done payroll before, you may have run into the 53rd instance of a payroll week. Today I've been editing my payroll template to compensate for this occurrence and I've almost got it complete but of all things to mess up on me, a hyperlink is the problem.
I have a legend where I can see all of the payroll week periods, the amount paid, PTO used, etc. And ALL of my payroll sheets are named "Payroll #1", "Payroll #2" ... "Payroll #52" and have a cell on my legend that hyperlinks to cell $A$1 of the corresponding sheet. But when I went to add in this 53rd week, the hyperlink has decided not to work. It will work if I name it anything else, and it will work if I take the # out of the name it will work, but I want to have the # in the sheet name because all other 52 sheets are named thusly and I would rather fix this one link than go back and replace 52 other working links and risk breaking something else.
Here is an example of one of my working links:
Screen Shot 2013-07-30 at 5.41.42 PM.png
I created the Payroll #53 the same way:
Screen Shot 2013-07-30 at 5.42.50 PM.png
But it always resets to this as soon as I try using the link:
Screen Shot 2013-07-30 at 5.42.15 PM.png
And pops up this error:
Screen Shot 2013-07-30 at 5.47.35 PM.png
I do have a VBA Macro that hides both that sheet, and the row referencing Payroll #53 on the legend by using a checkbox. I'm not sure why the # sign is making everything so difficult when there are 52 other hyperlinks that use it also. I tried deleting my macro to see if that was part of the problem but it still refuses to properly create the hyperlink. I also tried rebooting to see if that was part of the problem but it wasn't.
As a side note, this particular cell will not link to any page with a # in the name.
Screen Shot 2013-07-30 at 5.45.51 PM.png
I tried creating the hyperlink on a Windows based machine (since Microsoft is native to the OS after all) and it worked. So it must be a bug or something in the Mac version.
I have created a spreadsheet to work out deductions from gross weekly pay to give a net amount. I would like to be able to get from a net amount back to a gross amount but am unsure of how to do this.
Deductions are tax and national insurance. A person has a tax code for example 885 meaning they can earn up to 8850 a year before being taxed of 8850/52 a week so anything after 8850 is taxable. There are two tax rates (10% )and (18%). The band for 10% is 10500 per year or 10500/52=201.92 a week. This means any taxable pay above 201.92 is taxable at 18%.
To work out National insurance deductions for an employee, weekly gross amount- 100 x 10% giving the National insurance to be deducted. So Gross - Tax - National Insurance = Net amount.
Attached is a spreadsheet I created to work it out. Firstly does this look okay? Secondly is it possible to get back to the gross amount given ONLY a net amount and a tax code?
I have a problem in saving multiple pdfs from excel. I am posting a sheet here. First sheet has the employee details. Second sheet have the template for payslip. My aim is to save pdfs of templates of all the employee in the list using a macro.
currently i am doing it as
1. In sheet 2, I lookup each employee using the serial number of employees at the top left corner cell of the template.
2. Once the details are updated in sheet 2 i save it as pdf in my desktop.
The problem with recording macro and looping is that the pdfs are overwritting and the last pdf alone is saved. i could not find how to change the pdf name for every loop.
I am having a little trouble with IF. My pest control sales people are paid on straight commission. In addition to paying them a sliding percentage of the total contract value I pay them 50% of everything above $100 they charge on the initial service.
I am trying to calculate payroll. I have 2 columns where regular hours and overtime hours are manually inputed. They are then multiplied by their respective pay per hour columns to come up with regular and overtime pay. The next column adds these to get total gross pay. That gross pay is then multiplied by the FICA and FICA Med factors to figure those taxes(2 different columns). I then have a column the adds all deductions to get total deductions(Fed,FICA,FICAMed,St). The last column subtracts total deductions from the gross pay column to get net income. My problem is the net pay column is $.01 off sometimes.
I think what is happening is I obviously have all columns in dollar amounts with 2 decimal points. Some function columns have multiple decimal points in the answer and then are only showing the 2 decimal points. When those columns are used in the next equation, instead of using the dollar amount that is showing with 2 decimal points, it is remembering the multiple decimal points. This is resulting in being a penny off when I get to the end. How can I get the equations to use what actually shows in the columns(2 decimal points) instead of remembering multiple decimal places?
Each payweek I receive about 600 records some with duplicat names if an ee works overtime. I then look at previous payroll and extract a given account code. Because some ee's work overtime the rows do not always synchronize in count or names.
Any BEST lookup function? Or BEST tool? Is access a more logical option?
We are working on a spreadsheet that would project what our labor cost would be for next week. I need some help in figuring out how to calculate overtime when an employee reaches 40 hours.
I have multiple departments I need to keep track of multiple numbers with. For example, I need to see the cost of payroll for last fiscal year, vs this fiscal year and have excel show that as a gain (bad) or shrink (good).
I need to track sales this FY vs last FY, obviously, a gain is good and shrink is bad.
Finally, I need to track d&d goods this FY vs last FY.
Where I'm getting held up is, for example last year's payroll was 12000 and this year's is 12500 but next months is 11000 this FY vs 13000. How do I get excel to figure out that payroll decreasing is good and not just do simple subtraction or negative addition thereby allowing me to just plug the numbers in and excel figure out if what I'm throwing at the cell is bad or good comparing to last year?
Also, if I could figure out the if/then to show positive numbers green and negative numbers red that would be quite awesome too.
I need to determine federal tax withholding from gross pay.
If gross amount (for S-0 = single no dependents) is Over.................But NOT Over............Then Subtract.........And multiply $0.00...............$195.00.....................$5 1.......................10% $195.00............$645.00.....................$99 .......................15% $645.00............$1,482.00..................$351 ......................20% $1,482.00..........$3,131.00..................$447 ......................25% $3,131.00..........$6,763.00..................$849 .....................30%
If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?
I currently have an Excel payroll extract that populates a start date and end date via the calender control 11 user form.
What I have found out is I need to inject some sanity to this application. I can not have users select days that are outside of a pay period.
The users should only be able to select the first or the 16th of the month for a pay period start. Then they can only select the 15th or the 28th/31st for the pay period end.
What I am trying to do is have them select the month and the pay period start date, then the end date would automaticly be selected. But I don't want to have to create a bunch of loops to counter for the differing month end dates or leap years.
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
I have a payroll spreadsheet and I want to highlight cells that have hours entered for vacation. For example, in cell E4 I put the job name which is "VACATION" and in cells G4 through M4 I put the hours in for each day. (G4 is Monday, H4 is Tuesday, etc.)
I tried to put a conditional formatting using the following formula ="IF(E4=""VACATION"")" then I chose the fill color however it is not working.
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects() Dim MyCell As Range, oCell As Range, NewCell As Range Dim Rng1 As Range Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) ' Columns("B:B").Select Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "C1"), Unique:=True For Each oCell In Rng1 For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row) If MyCell.Value = oCell.Value Then................
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems Having many multiple day / date entries are the same date x 7 days Monday –Sunday (Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours 01/03/10, 9.5 hours labor, 3 hours travel 01/03/10, 9.5 hours labor, 3 hours travel 01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
I have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit Const Startingrow = 11 'Data starts on row ## Const AlarmDelay = 183 'send warning Sub CheckTimeLeftFac() 'References needed : 'Microsoft Outlook Object Library
Dim i As Long Dim j As Long Dim msg As Long Dim Lastrow As Long Dim WhoTo As String Dim SubjectLine As String Dim MessageBody As String Dim olMail As Outlook.MailItem Dim olApp As Outlook.Application Dim strLink As String
I have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
I am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)
I need the name of the day of the week from a date that has to be put together. This is going into a label on a UserForm.
I have a named range called wbDate. It is the month & year. The day of the month is the value of a TextBox (TBDay) on a UserForm. When I tried to combine these items, I used this
What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...
I created a pivot table with daily dates listed from 1/1/2009- 3/31/2009. 3 months worth. I see i can group this data by hours, days, months, quarters and years but what i really need to do is group this by WEEK. Does anyone know a formula I could use to accomplish this?
