Sorting Pre 1752 Years For Genealogy Purposes
Apr 29, 2013
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.
View 8 Replies
ADVERTISEMENT
Aug 28, 2007
I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies
View Related
Oct 19, 2009
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.
View 5 Replies
View Related
Nov 19, 2011
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.
View 2 Replies
View Related
May 16, 2013
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.
.lbs
.oz
5
[Code]....
View 1 Replies
View Related
Jul 18, 2014
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.
View 2 Replies
View Related
Jun 2, 2009
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.
View 9 Replies
View Related
Dec 12, 2013
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?
View 8 Replies
View Related
Nov 4, 2008
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........
View 9 Replies
View Related
Apr 21, 2009
how to change years in a row after i put in a year in K i have add the file
View 2 Replies
View Related
Jan 14, 2009
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
View 9 Replies
View Related
Feb 10, 2009
I have 2 dates..
09-feb-09
15-dec-09
Is therea way to calculate the number of years? this is equivalent to 0.27777 years.
Is there a way to get this with the datedif or another functiom?
View 9 Replies
View Related
Dec 15, 2008
I have list of dates:
e.g: D/M/Y
1/03/1997
22/05/2005
13/09/1945
I want a new list that just shows the year and is formatted as a number
e.g:
1997
2005
1945
Is there a way of doing this without doing it manually, I have 20,000 observations.
View 9 Replies
View Related
Aug 7, 2009
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
View 2 Replies
View Related
Mar 25, 2009
i am trying to make and age table to see if a player is too old by years, months and days all i can do is Years i need something like the following
1/10/2009 cut off date birthday is 8/5/1991
all i can do is the following
=YEAR(A3)-YEAR(A2)
View 2 Replies
View Related
Oct 14, 2009
Please refer to the attached screenshot of my working spreadsheet.
I'm attempting to concatenate lines 86 and 87, month and year together.
The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"
View 4 Replies
View Related
Dec 15, 2008
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.
View 2 Replies
View Related
Jan 15, 2012
I got a column with dates like:
07/01/11
06/30/11
12/31/11
01/01/12
02/14/12
etc.
Now i'd like to populate a combobox with only unique years [i.e. 11 and 12]
View 8 Replies
View Related
Mar 14, 2012
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.
View 4 Replies
View Related
Apr 4, 2012
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.
View 2 Replies
View Related
May 24, 2012
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.
Here is a sample file.
View 9 Replies
View Related
Apr 27, 2008
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........
View 9 Replies
View Related
May 20, 2009
In Column B I have various dates i.e [01/02/2008]. I Need a formula that will count the number of times the year 2008 appears in column B.
View 6 Replies
View Related
May 28, 2009
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.
View 9 Replies
View Related
Jul 4, 2009
I have the following:
A2= 01/20/2000 B2= 02/05/2000
A3= 09/05/2001 B3= 10/15/2008
C2 and C3 is where the answer will be.
what formula can be used to find out how many January 31th has passed between those ranges of years?
View 9 Replies
View Related
May 11, 2006
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.
View 3 Replies
View Related
Oct 5, 2006
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"
View 4 Replies
View Related
Dec 4, 2006
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.
View 9 Replies
View Related
Apr 12, 2007
Which formula should I use to return years and months between two dates.
4/1/05 7/30/25
View 2 Replies
View Related
Jul 30, 2007
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:
1) 06-Jan-06 -> 06-Jan-08 ( 730 days ) = 1.998175182 =>> divisor = 730/1.998175182 = 365.33333
2) 06-Jan-05 -> 06-Jan-07 ( 730 days ) = 2 =>> divisor = 730/2 = 365
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?
View 4 Replies
View Related
