Excel 2010 :: Grouping Dates Across 2 Different Years

Apr 7, 2012

I am using 2010 to write some code for a spreadsheet that will be used in 2007, so I know that RepeatLabels is not available.

My question is if you Group a date field by Months and Years and the data spans across 2 or more different years, is there a way to hide months in a specific year? I ask because the Date field has only 12 months in it and if I hide Jan (for example) it hides Jan in both years. (attached pic of filter for Date field below.)

Is there some other way to format it so it can discern between years or do I need to add yet another column to my original data?

View 2 Replies


ADVERTISEMENT

Excel 2010 :: Time Between Dates In Years And Quarters (.25)

Dec 18, 2013

how to calculate the time between two dates in years and quarters (represented in .25 increments). I would round down on the .25 increments (3 months) if not fully surpassed.

See attachment on what it should look like. C2 is my calculated column.

View 4 Replies View Related

Excel 2010 :: Grouping Pivot Dates By Group - Can Change Quarters?

Nov 2, 2011

One cool feature of 2010 is ability to (right click on dates) and Group them into Quarters.

How to change the Quarters to be in line with e.g. Financial Year, as opposed to fiscal years? i.e. have a start date of say June?

View 5 Replies View Related

Excel 2010 :: Rounding Number Of Years?

Sep 6, 2012

I'm using the following:

B23=IF(A23="","",DATEDIF(A23,I3,"y"))

Where:
A23 = a date of installation
I3 = TODAY()
B23 = a number of years

It currently calculates correctly if the number of years correctly if it's older than 1 year. If under one year, it yeilds 0. I would like B23 to show 1 if the current formula yeilds 0.

I want it to yeild a 1 if the current calculation is 0.

Windows 7 Ultimate / Excel 2010

View 6 Replies View Related

Excel 2010 :: Pivot Table Grouping

Apr 28, 2014

I had a workbook in Excel 2003 that i just moved to 2010. In the 2003 file I had 2 pivot tables, one each on a worksheet, looking at the same data, just grouped differently. One yearly, one quarterly.

Now in the 2010 workbook whenever I change the grouping on pivot table, the other one changes also. It's like they are linked together or something.

View 3 Replies View Related

Excel 2010 :: Grouping Information In Pivot Tables

Oct 24, 2013

I have a pivot table below: Excel 2010. All of the information within it is text, not numbers, as illustrated below as well.

Below is what I have now....

Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green
Column C, Row 2 = Green

What I want it to look like is below, removing the second word "green" from column C.

Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green

Is there a way to do this and do it automatically? If I was doing this in a regular excel sheet, I would have a merged cell in column C, rows 1 & 2.

View 2 Replies View Related

Excel 2010 :: Algebra Equations - Calculate By Grouping Like Numbers

Dec 31, 2013

I had a question about adding numbers, sort of like algebra equations (best description I can think of) into a spread sheet. Example:

Cell A1: 8
Cell A2: 8l
Cell A3: 8ls
Cell A4: 8l
Cell A5: 8
Cell A6: -where I want the total to be displayed in the following way: 16+16l+8l

Is this even possible to calculate by grouping like numbers?

View 2 Replies View Related

Excel 2003 :: Amortization Table - Years Elapsed From Column Of Dates

May 21, 2012

I am working with an amortization table and need to work out the elapsed time to amortize the loan.

Currently the worksheet is working fine and calculates the end date of the loan but the length of the column varies depending on the scenario.

I need a formula to calculate the time that elapses between the first date and last date.

The data starts in cell b13 and the last date could appear in pretty much any cell below that, so the formula will need to look for the last valid entry.

I am using excel 2003.

View 5 Replies View Related

Excel 2010 :: Dates As Headers In Table Not Recognized As Dates For Charting?

Nov 8, 2012

I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.

If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.

I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.

View 1 Replies View Related

Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies View Related

Grouping Dates In Pivot Data By Month As Well As Sorting Dates In Ascending Order

Apr 30, 2013

I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.

Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26

View 3 Replies View Related

Excel 2010 :: Calculate The Difference Between 2 Dates?

May 14, 2014

I have a pivot table and I created a formula that says:
=jobCompletedDate-jobCreatedDate

and so for example, I have
3/31/2014 7:21AM - 3/31/2014 6:33AM

and that difference is giving me: .03367

I'm not sure what this value represents......the difference in dates, converted to ??

View 2 Replies View Related

Excel 2010 :: Compare 2 Columns With Dates?

Dec 11, 2013

I have 2 columns directly next to each other each containing dates.

Example 1:
F2 = 5/23/13
G2 = 5/23/14

Example 2:
F3 = 6/6/13
G3 = 4/11/14

I wanted to do a conditional formatting that would highlight the cell in
column G if the Month/Day doesn't match the Month/Day in column F

Is there a way to do this with Excel only looking at the Day/Month and ignoring the year??

View 3 Replies View Related

Excel 2010 - Getting Average When Have A List Of Dates

May 29, 2014

Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?

2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014

[code].....

View 4 Replies View Related

Excel 2010 :: Matching Dates With Row Numbers

Aug 5, 2013

I have a column of Dates in Col B with corresponding X values in Col C. I specify a start date in Cell F14 and an end date in Cell F16. I then use the function shown in cell H6 to identify the starting row number (corresponding to start date) in Cell F6. If I used the same function to identify the end row (in this case 4/2/13), it would choose row 8 which is the first row in which it encounters that date. Unfortunately, I need the end row to identify the last row that date appears in. In this case, that would be row 11.

Excel 2010ABCDEFGHIJKLM1 Date and TimeX2
3/30/201334/1/2013 1:366.9744/1/2013 4:563.95 54/1/2013 4:573.27
Start Row64/1/2013 5:165.553=MATCH(DATE(YEAR(F14),MONTH(F14),DAY(F14)),$B:$B)+174/1/2013 5:172.35
End Row84/2/2013 17:191.59894/2/2013 17:192.81 104/2/2013 17:252.14 114/2/2013 17:262.05 124/3/2013 5:152.84 134/3/2013 5:154.11
Start Row144/3/2013 5:173.2404/01/13154/3/2013 5:173.45
End Row164/3/2013 17:124.0304/02/13174/3/2013 17:133.621819Sheet1

View 7 Replies View Related

Excel 2010 :: Sum Values Between Two Dates In Tables

Jan 27, 2014

Code:
tblCluster
Jan-14 Feb-14 Mar-14
Salary 10,000 20,000 30,000

Jan-14
Feb-14
Salary 30,000

I have a table above (in red), as an example, my actual table goes out 60 months.

Where the Green Value is, I need a formula that will reference the two months above it (They could be any of the 60 months), that will then sum the salary from the table between the selected two months (including these months)

I have tried,

Code:
=SUMIFS(C6:BJ6,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],">="&'Cluster Analysis'!$I$51,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],"

View 2 Replies View Related

Changing Dates To Years

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

Years And Months Between Two Dates

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

Dates, Number Of Years

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

Years & Months Between Two Dates

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

Sum If And Grouping Dates

Oct 28, 2009

See attached spreadsheet. I would like to take all the departments (column a) and all the dates (column b) and group them. Then add up the sums for columns (e and f) respectively. See desired result in spreadsheet for entire range. How do I do that?
This is in excel 2003. So cant use sumifs.

View 4 Replies View Related

Grouping The All Dates

Dec 28, 2009

I have my date list in a2:a503 with weekly dates ie. 2/12/2009, 9/12/2009, 16/12/2009 etc etc,
what I would like to do is 1st group all these dates as dec 09 in b2:b503, 2nd point is I would like to have cell c2 to have lowest date (in my example 2/12/2009) and d2 to have the highest (which is 16/12/2009) of that month

ie start date 2/12/2009 end date 16/12/2009.

View 9 Replies View Related

Excel 2010 :: Creating A Chart With More Than 2000 Dates (each Day)

Apr 26, 2014

I am creating a chart with more than 2000 dates (each day). How can I format the axis so it only displays the 1st day of each quarter (01/01/2010, 01/04/2010, 01/07/2010...).

At the moment I cannot format the axis and minor/major units remain numbers.

I am using Excel 2010 and European date format.

View 2 Replies View Related

Excel 2010 :: Filtering Dates And Counting Occurrences?

May 28, 2014

I am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.

I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".

For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.

The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.

There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.

View 7 Replies View Related

Excel 2010 :: Total Entries Between Two Dates In Table

Mar 17, 2014

I'm an Excel newbie using 2010, but have been given a rather daunting task. I have a lot of data organised in a table, something such as:

Column A: Name, Column B: City, Column C: Date Entered

I can obviously filter this table to show the values I need, but I need it to automatically tell me the total amount of people entered per week, per city on a separate worksheet.

I understand I could use a pivot table for some of this, but I want to further automatically manipulate the data once I have the totals (for example, adding a financial value for each, etc) which I'm not certain how to do with a pivot.

So, ideally what I would like is a formula that says total entries in Table 1 that are marked Sheffield in Column B and have a value including or between 01/03/2014 and 07/03/2014 in Column C.

View 3 Replies View Related

Excel 2010 :: Conditional Formatting On Expiration Dates?

Jun 21, 2011

I have a spreadsheet where we track our contractor's Worker's Comp and General Liability insurance certificate expiration dates. I want the expiration date to highlight in red if it is expired and to highlight in orange if it will expire within 30 days or less.

I have attached a testing sheet similar to what I'm working on. The F column has the dates that need to highlight. I have MS Excel 2010.

View 6 Replies View Related

Excel 2010 :: Dynamic Dropdown List Of Dates?

Aug 9, 2013

I'm trying to create a set of three dynamic/dependent drop down lists of date values, based on a set of imported data. Depending on whether the preferred choice is to list all dates in a range or just dates on the 15th, the available start and end drop down lists will change.

In addition, if possible I would then like to display a list of valid dates (not sure if this needs to be VBA).

The attached sheet shows/explains what I am attempting but here it is as well:

If B3 is set to show all dates in the month then:

the first dropdown option for C3 will be the first date of IMPORT (D2)
the first dropdown option for C4 will be the C3 value, to the last date in IMPORT (D) column

If B3 is set to show 15th of the month then:

the dropdown for C3 will be the first date of IMPORT (D) column with a date of 15th the dropdown for C4 will be in the range of C3 to the last date of IMPORT (D) column with a date of 15th only dates of 15th will be listed in both C3 and C4 dropdownlists

General

the dates in IMPORT column D are taken from IMPORT column A, B & C, which will be imported and will always be date sequential the number of rows of date entries will change with every import but there will never be any blank rows between dates the values in IMPORT columns A, B & C will always be numerical, and can be referenced if required [ideally] the LIST OF DATES will be populated with the date range, based on the option selected in B3

I'm fairly familiar with Excel (2010) and I have also done some VBA but I'm new to dynamic, nested dropdown lists and I'm unsure what the most practical way is to achieve this.

View 1 Replies View Related

Excel 2010 :: Changing Format Of Dates In A Column?

Oct 19, 2013

I am working with a 2010 Excel spread sheet and need some direction.

One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.

I need all of the dates to be in the first format (dd/mm/yyyy).

Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it?

View 14 Replies View Related

Excel 2010 :: Identify Range Of Dates For Given Date?

Jul 26, 2012

I've gotten the desired result in Column B, but this will not work going forward as we add to the table in columns E:G .

I'm looking to search between columns E:F, Identify the date-range where my dates in column A belong, and pull the corresponding rate from column G into column B.

Excel 2010
A
B
C
D

[Code]...

View 8 Replies View Related

Excel 2010 :: Code For Inserting And Removing Dates

Dec 14, 2012

I need a code that when i place a date in a cell D10 (Example:25-January-2013) it will then add 40 days of dates daily to AP10.In D9 can it also add the weeknumber (every 7 days the weeknumber increases by 1) corresponding to the day date in D10 (iso).Can this be attached to a button.Enter the date in D10 then press the button and the dates auto insert across the sheet daily to AP10.

Can the button say ADD DATE or REMOVE DATES.First date in D10.When the button says REMOVE dates all dates deleted when button pressed and cell D10 then says "add date here".If no date is placed in D10 and ADD DATE button is pressed a warning messagebox appears and says NEED TO ADD DATE .Will not work until date entered.When date entered in D10 "Add Date Here" disappears until REMOVE Date button is selected and again "Add Date Here" is displayed....not sure if this is possible but would be good if achieved. Excel 2010

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved