I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use today’s date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date.
I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days. I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically.
I have been playing around with this formula:.....
All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month. Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc.......
Trying to calculate the lost revenue value for when a piece of machinary is offline for a period of time based on the hrly price during that particular period. The machines come on/off line at various times during the year so i am trying to make this a bit dynamic so i only need to add new info and not have to change the formula
Column F - Off date of machine Column G - Restart date of machine Column H - Hrly time Column I - Hrly price
I thought this formula (an array) would work for me
=average(if((Column H>=Column F) * (Column H<= Column G), Column I)) but it only calculates that average price for the first period and not new average prices for other periods.
what adjustments I can make to my formula or is there a better formula to use?
Our company has been taken over by new mgt (Y co) on 1st Nov 2012 from old mgt (X co). Y co has the policy of providing employees with EOS benefits as mentioned below:
1st 5 yrs - 1 month salary per year 5-10 yrs - 1.5 month salary per year +10 yrs - 2 months salary per year
But for deciding the slab, total period of service is considered. The below table provides the calculation of days as on 31st Dec 2012 based on different joining dates which I need to fix by way of formula.
Name Hire date Salary Total service (days) X co Y Co 1st 5 yrs 5-10 yrs +10 yrs
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
I'm using this formula (below) to add the hrs between 2 time periods (4:30 pm in cell I37 and 8:00 pm in I38, then the formula is in I39) and works almost every time even when the second time goes into the am hours, except in certain instances such as: 4:30 pm to 11pm show as 30.5? But sometimes it shows the correct number, 6.5
I'm trying to calculate the total hours worked for two given periods over a shift , which can span two consecutive days ie. start 15:45 and finish at 00:15 the next day. Hours worked between 6am and 6pm are paid at standard rate, whilst hours worked between 6pm and 6am attract penalty rates. Hours are cacluated in 24hr time
I have attached a copy of the timesheet that we use so you can see exactly what I'm trying to achieve, and included most of the shifts that we have.
When using the Trend function, I can get the value for individual future periods. I then can get a total for the year by summing each individual future period.
What I don't know how to do is sum them all using one formula. For instance, If I know the values of periods 1-5, how would I write a formula that would sum the future values (in one cell) of periods 6-12 -- without having to sum each future period individually?
I am having trouble writing a Macro to delete multiple periods at the end of each cell. Each cell looks simiar to this: Blah Blah Blah................... I am using the InStr function to find the first period but it returns a 0 everytime. Here is the code I used to find the period. I want it to delete everything after the first period and store the value back in the same cell. Right now it just stores blanks.
Sub PeriodRemover() Dim NewContract As String, Contract As String Application.Calculation = xlManual i = 2 Sheets("Product Listing by Contract").Select Contract = Range("A" & i).Value Do Until Contract = Empty NewContract = Left(Contract, InStr(Contract, ".")) Range("A" & i).Value = NewContract i = i + 1 Contract = Range("A" & i).Value Loop Application.Calculation = xlAutomatic End Sub
I have a text value which consists of two periods ".". I want to pull the value in the middle. eg. From the text value in a cell "oakland.california.usa", I want to pull "california" in another cell. All the values in column are in similar format.
A B 1 oakland.california.usa california
Hope this is not duplication of any pre-existing thread, if it is, I probably don't know the correct keywords to query.
I am trying to find a way to identify the nmber of periods where a particular value is exceeded.
Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days.
Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods.
I have half hourly data for five years, in two columns A: Date and B: Load in Kg. The load on the beam is not uniform over time and shows a cycle of load.
In Column C I have an AVERAGE function that returns the average load based in the 4 hour period (2 hours backward looking and 2 hours forward looking) if it is less than 50% of the maximum load.
The data in Column C returns sets of continuous vertical cells of data showing periods of low loading and empty cells where the load is greater than 50%. The sets can be as small as one cell (i.e. one hour) to a set of contiguous vertical cells for one week of low load.
My objective is to use Excel to analyse all the sets of contiguous cells in Column C to determine the time period from the first cell of each set to the last cell of that set and drop the result into another work sheet (pivot table) to further analyse these results.
How do you remove the periods in this numbering sequence 1.02.03.04.05 with the end result being 102030405? Note also that the colum contains various combos that can be any combination of numbers from 1.01 to 1.09.00.00.00.00.00.00.99. I use both versions 2003 and 2007....
I have a spreadsheet that holds a list of customers and the contracts they have with my company - this sheet includes the Total Contract Value in £s - so how much each customer will pay us for the service - the period of the service - so the start date of 01/01/08 to end date of 31/12/08 for example. What I then have is all the months across the top of my spreadsheet and I need to extrapolate the contract value, based on the period between the months.
Initially I had complicated formulas that simply divide the value by the number of months of the contract - however, my company has since changed the policy and requires it to be done to the day.
For example, if the value was £12,000 and the period was 12 months from 01/01/08 - to calculate January 08 it would be - 12,000 / 365 * 31, Feb would be 12,000 / 365 * 29 and so on...
Does anyone know how I can do this - bearing in mind that each contract won't be as easy as 12 months - it may be start date of 15/06/08 to 08/10/10 for example... If anyone can help me do a formula or formulae to calculate the value for each month - I would be very very grateful - as always... [/img]
I want to count the number of sales in three periods. prior 7 to 12 months, prior 4 to 6 months, and over the last 3 months.
I have three letters that occur in column B of sheet 2.
A for active, P for pending, and S for sold. The date of activity appears in column C for each event. It is in mm/dd/yyyy format. Currently I have over 5000 rows.
I would like to total the sales for each period and place it on sheet 1. can you help or point me to the right place to read up on it. I can get the information by using a pivot table but there has to be a faster way.
I am trying to plot two data series in one chart. The first series has monthly corn prices for last 10 years (~130 data points). The second series has bimonthly chicken prices over the last 10 years ( ~72 points). How can I plot them together? The bi-monthly periods for the second series do not exactly coincide with calender months ( e.g. Aug 19 to Oct 13, 2007). As a result, I can not adjust the points to match with calender months.
S1 - "End" No. | ID | Name | Login | Total | Jan-07 | Feb-07 | March-07 | => | Dec-07 S2 - "Datasource" ID | Irrelevant Name | Period | State | Irrelevant Data
I need something to go through the unique IDs in the "Datasource" & find a match in the "End" spreadsheet & then insert the number "1" in the corresponding time period (matching Period with Jan-07 or which ever it's suppose to represent). The Period is written in the following format:
122007 for December 2007 12008 for January 2008
One thing, the datasource has dates for both 2007 & 2008. The end spreadsheet has 2007 in one worksheet & 2008 in another.
I'm trying to calculate the length of a work order to develop an average and future proposal estimates.
For example: Job received = A1 In work = B1 Job completed = C1 Total days to complete = D1
I know I can enter in D1 C1-A1 and get the correct result but I want the field to calculate A1-TODAY() until a completion date is entered. This would display the number of days the job has been in work until completed. Once completed use the C1 for calculation.
Sorta like If(C1=">=0" then (A1-Today()) else C1-A1)
I will then use conditional formatting with block colors to indicate whether it is a completed job or in work job.
I have a (very) large spreadsheet which contains a column of numbers. I am trying to work out a way to identify all of the times when this column has 3600 or more consecutive zeros. Ideally, I would like it to tell me how many and the size of these periods of 3600 or more consecutive zeros, but I would settle for something as simple as just highlighting these parts.
I have spreadsheets with a lot of dates where the date separators used are periods “.” instead of slashes “/”. For example, 12.02.2008 is what shows in a cell. Where I live (Australia) that means 12 Jan 2008. My need is to have Vba code that converts these to dates; e.g. 12/02/2008 or 12-Jan-2008, etc. I can manually select the cell or cells, press Ctrl+H, replace the period characters with slash characters and it works correctly - I get 12/02/2008 – which I can then manipulate as a date. However, if I record a macro of the above actions, when I run the code the result I get in the cell is 2/12/2008 – i.e. 2 Dec 2008. The code recorded is:
The only time the code gives the correct result is when the month is greater than 12; e.g. 23.02.2008 is converted to 23 Feb 2008. My computer is set up with regional settings to Australia and date format day/month/year.
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd
I desire to look in column "E" and I have 3 different variables. "J", "K", "L". If column "E" has a j add 30 to the years; K add 35 years and L add 38 years. for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
I am attempting to create a formula that will count the number of times, lets say letter A, occurs in column E. However, the tricky part that I have been confused on is that I only want to count how many times A occurs between each hour of the day. I need to count number of times "A" occurs between each hour of the day for the entire month.
My Worksheet looks as such: Column A contains the dates for the month of November, Column B contains times that random instances occur, Column C is not important, Column E contains a Letter A-N that pertains to what occurred at a time in column B. SO, Column B and column E coordinate with each other.
Here is an example. Column A - Cell A1 - 11/01/2013 , A2 - 11/02/2013, A3 - 11/03/2013. Column B - Cell B1 - 12:01 AM , Cell B2 - 14:03 , Cell B3 - 15:23 Column C - Cell E1 - A , Cell E2 - A, Cell E3 - B
So I would like Column F to display that between 12:00 - 12:59 Am there was X number of times the letter A occurred throughout the entire month.
(1) I want to find the maximun number for the past n periods in a range of cells, but I want to be able to vary n by changing the number of periods in one cell at the top of the sheet, i.e. if i enter 10 in cell A1 the function will return the max number for 10 periods over a certein range, if I change the figure to 20, then the max number for 20 periods will be returned!
(2) I have a column (lets say column c) which will return a number of values if my criteria are met and 0 if not, I want a function in another column which will replace the 0 with the next non 0 figure in column c, the gaps between the non 0 figures can vary. I feel it involves looping in some form, and writing a new function, but I am not sure.
'Pivot Table Calculated Fields" - Below is my pivot table.
I need the pivot table to subtract 'February' sales figures from 'January' sales figures to get the difference (variance).
I know the following steps:
1) Click on the Pivot Table
2) Choose "Formulas"
3) Choose "Calculated Field"
4) In the "Name" field - type in the name that I want "Two Month Variance" (for example)
5)......then I don't know what to do
Sum of Quantitymonthsupplier idjanfebGrand Total 754466163150842519337568263901785022875682639410025035075682639500 75682639642523866375682639780012492049756826398209209756826399292292 756826400264264756826401178178757494037292292761034919209209867233456264264(blank) Grand Total245154236931
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were: Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation.
The first column is the Total Payment and the second is Inducements.
Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.
i got a problem to calculate IRR and NPV for my company cash flow. i so confuse how to calculate cos the initial investment (expense) is pay in installment by yearly basis. i hope anyone can help me to solve this problem.
I m not sure whether what i'm doing is correct or not. thanx