I am trying to sort by years (I have 66230 records to sort on) Parish Records that date from 1538/1659. The year as we know it now - did not come into existence until 1752 - and i have been trying to sort my data on the "years". I know i have done this before - however, i have either forgotten how to do it - or the Excel 2010 was not the version i accomplished this on.
Basically i have this:-
25 Sep 1596/97
25 Jun 1596
05 May 1611
10 Feb 1611/12
When i do a normal "sort" the split year comes before the main year as in 1596/97 comes before 1596 - thus putting all the dates out of sequence.
how to over come - i am no good with macro's - so if it can be done with using sort - it would be easier for me.
I am trying to come up with the appropriate formula that can be used for possible forecasting purposes. Countif proves to be futile due to more than one criteria that needs to be met. Sumproduct seems to be more feasible but I am unable to get the correct formula or maybe I am wrong.
Here's the objective...I need to be able to plot the total number of people working on a project on a daily basis - # of people working on a Monday, Tuesday and so forth by means of referring to start and end date column..I am open to revisions so as to streamline everything.
having an excel file autoname cells in order that I can hyperlink a picture to each tab individually.
Example - The file has autonamed tabs Wednesday 1, Tuesday 2....etc. In each tab is a reservation guide for three restaurants and the logo's at the top of the tab should hyperlink to a cell within the same tab. Blu Restaurant = A32, Grille = A223 within the same tab. How can I do this without manually naming the 3 cell's in each tab? I will also need this to be flexible so that when I create the January worksheet it populates from the tab names that are created using VBA already.
I am looking to sum the weight I add in my packing list spreadsheet.
below are two ways i have been working with and will settle with what ever i can get. all i need is to be able to enter the weight of a package either by lbs.oz or in separate columns (lbs)(oz) then have a formula take either way and know there is only 16 oz in a lb and give me a total weight of all things shipped.
Excel spreadsheet, that contains several columns. Three of these columns contain text data that is selected by using a simple drop-down menu sourced from a list. If an attempt is made to enter text data not in the associated list an error message is displayed. Easy so far.
I need need to extend the text data validation across all three columns so that only specific combinations of the text in the three columns can be used which is reflected in a further list. These combinations will be counted elsewhere and so needed. Therefore, if i attempt to enter a combination of text columns that is not reflected in the list, this too will present an error message stating 'combination not valid', or words to this effect.
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1).
So, the problem comes when there is a need to select a data range which is not that well defined like $B$6:$B$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.
Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... there is no data in there so feel freel to fill it with any numbers you like.
I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?
I am working on a retirement calculation sheet. For retirement calculations the employees need to figure out their highest yearly income for three out of their last ten years. The three years can be based on any consecutive twelve months but each of the three years must be the same months, (example, Jan thru Dec, or July thru June). The three years cannot overlap. Here is what I have so far. Column A, paydate. Column B, amount. Column C, year to date amount. Column D, amount for past 12 months. I would like to list the three highest years in the first 3 rows of Column E. Any help is greatly appreciated.
Pay Date Gross Year to Date Gross Past 12 Months Highest 3 Years 08/10/06 3,594.58 3,594.58 3,594.58 08/24/06 3,332.79 6,927.37 6,927.37 09/07/06 2,595.59 9,522.96 9,522.96 09/21/06 2,457.36 11,980.32 11,980.32 10/05/06 2,457.36 14,437.68 14,437.68 10/19/06 2,457.36 16,895.04 16,895.04 11/02/06 6,212.51 23,107.55 23,107.55 11/16/06 3,206.09 26,313.64 26,313.64 11/30/06 4,104.56 30,418.20 30,418.20........
I need to calculate YTD sums using the data that spans three years. I am using
SUM(B2:INDEX(B2:AC2,MATCH($B$10,$B$1:$AC$1,0))). This works fine if all of my data is contained in one year. However, when my data spans multiple years it does not. For example, if I have the following and today is April 2007 I want to calculate Jan-April 2007.
Labor Type09/0610/0611/0612/0601/0702/0703/0704/0705/0706/0707/0708/0709/0710/0711/0712/0701/08 Bus External2404526907569511,2018808059439237781,1216191,0639378001,156 Bus Internal1,4412,7942,9992,5914,0093,9444,2714,3254,7593,5493,0172,4371,9262,9722,3711,7582,931 IT External1,3483,3993,8653,8586,0247,75510,11110,40911,86911,42311,29710,4878,32713,13412,96912,41917,265 IT Internal3,3537,7428,7107,88412,73112,74614,91114,66215,30413,80513,13511,8248,63511,7728,8876,80911,283 Staff Aug12138195169195408796641626340403428469675604395637
The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference
I have two dates, one is a start date the other is todays date, I want to subtract the start date from todays date and show the number of years. But a small twist is I only want to take the years away from each other, ignore day/month. Start 01/05/2000 todays date 01/10/2008 years = 8. Start 10/10/2000 todays date 01/10/2008 years = 7, want it to still show 8 years and ignore the start day/month.
I have a spreadsheet with my students that needs to find the date a person will be 18. based on their date of birth. I have =B27+(365.25*18) where their DOB is in B27. I have had several instances where it is correct and several instances where it is a day off.
I have been trying to figure out a formula to convert the amount of months in a certain cell to years.
I have come up with:
=INT(E29/12)&" years "&MOD(E29,12) & " months"
The problem is it comes up with 2 years 3.37063951499227 months
How I change it to come up with 2.4 months, round it to the highest number instead of loads of decimal places? I have tried change cell E29 do 0 decimal places but it doesn't work.
I need to calculate a quote over 4 years but the first and last years are not full 12 months.
On Row 20 are monthly costs for each year. The start and end date is in Row 3.
I need to find a formula that can calculate ANNUAL TOTAL for the all these year in Row 22 populated automatically, not just manually times the months in each cell.
I created a wedding list with a bunch of fields for each household: first name person 1, second name person 1, first name person 2, second name person 2, street #, street address, apartment, city, state and zip. Then I realized that I probably needed to combine fields for each line of the address so I created 3 combined fields: 'combined 1 ' that looks like this "joe smith & patsy cline," a 'combined 2,' that looks like "14 jones street, #3," and a 'combined 3,' that looks like "New York, NY 10037."
I haven't printed to labels for years and when I did some quick research via the help function on excel it said to print labels via word...which worked fine until i got to this section: " In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help. "
I have no idea what this means, but here's what I need to figure out: " how do I print labels using just 3 of 8 existing columns in my spreadsheet, i.e I just want to use combined 1, combined 2 and combined 3 for the 3 lines of the address. My first instinct was just to cut the contents of the combined fields into a new spreadsheet and just do it taht way, but I created the combined fields using a formula which relates to the other fields........
I am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.
I'm wondering how you can quickly create a row of labels holding Year 1, Year 2, Year 3, etc. I used to know how but I can't seem to remember anymore and can't find how to anywhere.
I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.
=TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"
I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.
It looks like a pretty silly problem but it's driving me mad. The yearfrac function takes a two dates and a basis. Now assume basis = 1 ( Act / Act ) and two cases:
In case 1) 2008 is a leap year ( but the "leap" has yet to come! ). Can anyone explain me the logic behind? I suppose the extra day is divided by 3 but where is the "Actuality" of the divisor?
