I work in a large Public Service Payroll Department. I am trying to create a spreadsheet that will automatically calculate arrears payments for salaried staff. What I need is something that will make the calculation after I provide a start date an end date and old and new salary per annum. I'm self taught using excel but at a very very basic level.
My problem is that I don't know how to enter a formula which will give me the correct amount between 2 dates. For example
01/10/2005 to 16/09/2005 old salary PA 16000 new salary PA 17500. The manual formula would be:
((17500 - 16000)x1/12) + ((17500 - 16000) x 1/12 x 16/30)) = £191.67
I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
Example.xlsI have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet). I need to calculate in excel the highest 5 consecutive year average salary for each (if they have less than 5 years with salary, then it should just average the years the do have). Some people have breaks in service (for five years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
I own a local restaurant and want to streamline my excel spreadsheets. Currently, I have yearly workbooks (2005, 2006, 2007, etc.) that have all of my revenues included in them, split up monthly by worksheets. I then have lines for each day within the month for the revenues earned at that day.
So, if I want to find how much revenue I earned on March 1st, 2006, I go to my 2006 book, March worksheet, and look at the 1st. Here's where my problem lies.
I want to create a new spreadsheet where all I have to do is type in a certain date (say March 1st, 2006) and all of the information for the date will be automatically displayed on the new sheet.
I am looking for a formula which will compute interest earned (not ending account balance) for the following scenario:
1) Set $ amount (say 19250) is deposited on January 1.
2) Annual interest rate is .95%
3) Interest is compounded daily
4) Fixed withdrawals of $1750 are taken each and every month on the first day of each month
What is the total interest earned on the account? Basically, it is a issue where each month diminishes the "pool" of money earning interest. I get what's going on conceptually, but can't figure out which Excel function to use.
I am prepare budget salary base on actual joining date and budgeted joining date. I can only manage to get the formula by joining month not by joining date
I am working on a budget spreadsheet and want to find a formula that will automatically tell me what my take home pay is depending on the yearly salary and see if covers my expenses (linked to another spreadsheet). It will be easier to budget my expenses depending on my salary. My attempts to create IF and = haven't worked.
Is there a way to create a macro that will change 1 of the Base Salary(s). I need one of them to be Base Salary - Admin, or Base Salary ' or something different for my vlookup.
Will this macro work for each different tab I have?
I have this table which has employees names with basic pay and contribution.
I was tasked to create a excel sheet where in if the employees basic pay is input, the corresponding contribution for the employee will show depending on which salary bracket he/she is on.
I am not allowed to know what my workmates salaries are since im just an associate, so i just have to leave em blank and wait for my manager to input their salary.
See attached file for the workbook : PHEALTHWORKBOOK.xlsx‎
I can calculate the networkdays between two dates but how do I project a finishing date if I know the starting date, the holidays, and the duration of the task in working days? (Sample sheet attached, working in Excel 2003).
I need to calculate the amount of time between two dates, and show it in the number of hours and minutes. My dates are formatted to show the time in Zulu time. So 1406 = 2:06PM.
Here is what I have:
A1 = 6/4/08 1406Z B1 = 6/5/08 0402Z
For this example, the formula should return a result of 13 hours and 58 minutes. With the result, there is no need to desingate Zulu time with a 'Z'. But I need the result in a format that will allow me to calculate an average for all my data.
I would like to calculate the total hours between two dates using a unique formula and without inserting new columns. I have attached an example of the data I'm working with.
In my excel I want to use a calender date such as 05/21/09 and automatically add or subtract a specific number of days. For example: 10 days before 05/21/09....
I dont want to have to count back 10 days and manually enter it. Is there a way to have excel calculate and give me the date in my spreadsheet? i.e. automatically calculate (05/12/09)-(10 days)= 5/11/09. its not too hard when its 10 days, but when its -97 days, it can be very annoying.
I am trying to figure out what formula to use to calculate the difference between the creation date and resolved date in hours and days. That is, In once column I would like to see the difference in 'hours' and in another column I would like to see the difference in 'days'.
Calculate dates as The Number Of Days, Months Or Years Between Two Dates
The two formulas I tried below show the wrong output when trying to figure the days between 12/8/2011 to 1/2/2012. It should show 0 years, 0 months, 24 days, not 138 days. I did notice when the day of the month on the start date is before the day of the month of the end date, the formula seems to work. Ref. the second dates on the first example.
Example 1 Start End 12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Wrong display. should show 0 years, 0 months, 24 days
Start End 12/1/2011 1/2/2012 = 0 years, 1 months, 1 days
Note that when the start date "day", (1), is before the end date "day", (2) the formula seems to work.
First formula used above: =DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"y")&" years, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"ym")&" months, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"md")&" days"
Example 2 Start End 12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Should show 0 years, 0 months, 24 days
Second formula tried above: =DATEDIF(B10,C10,"y") & " years, " & DATEDIF(B10,C10,"ym") & " months, " & DATEDIF(B10,C10,"md") & " days"
I seem to have the worst luck when it comes to getting dates and times to calculate together. My logic just seems to go straight out the window.
What I am trying to do, the end result, is get a Time Worked (duration) that I have spent on a ticket, and a Total Time, the time a ticket was opened, to the time it was closed.
I guess we can try to do this one step at a time.
The first thing that I would like to know is if there is a way to calculate Date/Time in a MM/DD/YYYY, H:MM format?
IE: Ticket Opened ...................Ticket Closed 5/16/2008 2:54 PM..............5/19/2008 10:47 AM Total Time in Days / Hours / Minutes
Right now I have the Date and Time split into separate cells, simply because I couldnt figure out a way to subtract the combined date and time.
I am trying to map the total networkdays across the months of the year for a list of contracts with variable start and end dates.
Example of the results I'm after:
Contract StartDate EndDate January February March etc. Contract1 15 Jan 07 13 Jul 07 13 20 20 Contract2 5 Feb 07 2 Mar 07 0 18 2
I assume that the add-in NETWORKDAYS function is involved (and I'm ordinarily comfortable with using this function with only two dates and my named range of holiday dates), but I can't seem to figure out how to use it in this more complicated way. My own extensive struggles with it have led to ridiculously long and nested formulas that even I didn't understand after a few days away from them. I've searched the posts and seen questions similar to mine, but not close enough to give me a solution.
I am building a template to auto populate budget value by straight line based on task start/end date criteria. some tasks could give me correct duration distribution, but some don't. I am looking for If function, which could give me correct monthly allocation budget. Here is an example of what I am trying to achieve. task 0110 duration is from 09/15/07 to 05/24/08, duration month is 8. budget $34,465supposed to be allocated to 8 month starting from Sept, 07. but my if logic return 9 month instead.
I have a countif formula that calculates the number of entries between 2 dates. However i now need to also count how many of these match a 3rd value. So i will need to work out how many "are greater than or equal to B2" "less than or equal to C2" and "equalling D2"
what i have already =(-COUNT('Current Work'!AE8:AE2000)+COUNTIF('Current Work'!AE8:AE2000,">="&B2)+COUNTIF('Current Work'!AE8:AE2000,"<="&C2))
I am using Excel 2007 and have to send monthly payslips to respective email ids. I have the Name and Email ids in Sheet 2 Range B1:C59 , and employee codes in A1:A59. And in Sheet 1 i have the Payslip format which was automatically displayed when i select the employee code from drop down list in the cell E7 in sheet 1.
Every time i manually save the files as PDF and send to their Emails. I want the out put as whenever i select the employee code from drop down list , it should automatically save as PDF with Name of that employee ( Name of the employee is located in B1:B59 in Sheet 2 ) and attachment should automatically go to that employee's email Id.
how to calculate the difference in value of an item between two dates on a pivot table. I have one table that feeds the Pivot Table and it has three fields:
Data as of Date Item Value
So each day, the Data as of Date is updated to the current day and the items are the same but the values are different. I need to calculate the difference in value between the most recent date and x days ago. I cannot figure out how to do this via Pivot Table.
I am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.
I'm just trying to calculate the difference between dates in two columns and applying conditional formatting based on if it is a positive or negative number. But when there is a null value in either of the date columns I don't want anything returned.
So far I'm either getting a 0 or a FALSE.
Column A = Date 1 Column B = Date 2 Column C = Formula
