The following formula is in cell A3: A3=IF(A2="X",A1+365,""). Rather than A3 displaying only 1 year, I would like for it to update after that day occurs to =A1+730, and then A1+1095, and so on.
View 2 Replies
I am trying to create a formula which would display a date (prior to the computer's system time) when a certain amount of text occurs.
I have attached my workbook with an example and further explanation.
I am looking for a formula which will find the oldest date for a specific set of search criteria:
A1 A2 A3 A4 A5 A6
05JUL2008 06JUL2008 07JUL2008 08JUL2008 09JUL2008 10JUL2008
B1 B2 B3 B4 B5 B6
Cat Dog Pig Cat Pig Dog
So if possible I would like a formula that searches through and returns
C1 (Search for Pig) = 07JUL2008
C2 (Searcg for Cat) = 05JUL2008
C3 (Search for Dog) = 06JUL2008
what formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.
View 3 Replies View RelatedI have two problems with my PivotTable: 1. My PivotTable consists of a constant number columns and varying number rows, eg.......
The dates (with data) should continue to update as I add new date records to the source datasheet, but they don't. I try the refresh option for the table, but it only updates for the dates that it was designed for. I also have automatic updates set, but so far nothing is working. I have to rebuild the PivotTable each day after I add new data...
2. Second problem are the date labels on the rows. I need to have them include the literal day of the week such as
Thursday 01/01/09
Friday 01/02/09
Another less urgent problem that I would like to solve is that I have included additional percent calculations for the total column that I am having to rebuild each time. Can that be included in the PivotTable as well?
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies View RelatedHow to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?
A1: 27-July-2012
Answer D1:JULY' 2012
Pl note "' " is suffixed after JULY (the month).
i have the following table of information
Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93
and a list of dates i need to look up the date to see which year it falls in and return the year
DATE function won't return TODAY()'s year in the "year" slot.
Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?
I would like to see the Year To Date sum month by month in cell B8 when rolling down the cell B6. See in attached file.
View 9 Replies View RelatedI am trying to create a chart which updates every time a new date (and its data) is added to my list below:
Date
Clicks
Impressions
Click Through Rate
01/06/2012
3
93
3.23
02/06/2012
4
100
4
03/06/2012
2
102
1.96
The chart plots the number of impressions and the Click through rate (on a secondary axis) against the date.
I think I need to use the offset property but am not totally sure how to do this
i have attached a sheet, i want if i put date in cell a2 and automatically month should come in cell b2 and year in c2,
LIKE JAN AND 2009 LIKE THAT
I am trying to change the year of a date range I search from.
I have a selection of dates, I use sumproduct on a seperate page to calculate the number of entries between two dates entered in seperate cells
=SUMPRODUCT((HFRA!B4:B2000>=Summary!B1)*(HFRA!B4:B2000<=Summary!B2))
Cell B1 conatins the date 01/01/2007, and cell B2 contains the date 31/01/2007. I repeat this for every month of the year.
I want to be able to change the year of these dates in B1 and B2 from a seperate cell on another page.. so I can change the search ranges to 2008 without going into each cell and changing it manually.
I have a column with dates of July 1 of every year. How can I use conditional formatting to shade a cell a certain color based on July 1 of every year? The cell for July 2013 should be shaded, when 2014 comes, it is no longer shaded, then 2014 becomes shaded, & so on...
View 1 Replies View RelatedI am trying to simplify a spreadsheet. I have a column with dates from last year and I was wondering if there is a formula that would automatically change the color of the cell once the date is over one year to the day to show that the date in the cell has expired?
View 4 Replies View Relatedis there any way to remove/deactivate the cell's auto complete year function?
eg: input the value "03-jan" and the cell will automatically convert the value of the cell to 03/01/2008
Three columns.
A - Date last checked
B - Due Date
C - Actual Date checked
Currently column B is formatted to Date and simply has =A+84 and will display a date 3 months in future. However if there is no date in column A, then column B displays a default 1900 date.. Is there a way of making this blank if there is no date in col A?
I am entering data for ships arrived every month. There are about 200 records to be entered and for every record there are 4 date fields.
My question is : Is it possible to set default month (for ex = July) and year ( for ex = 2013 ) in these 4 cells so that
If I just type 16 it should be 16/07/2013. Because all my dates will be within that month July 2013.
Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.
Suppose G2 stipulates whether of car insurance repair is comprehensive or third party (Comp, TP). L2 contains an estimate date for the car.
If the claim is Comp the authority for the job will always follow 2 working days later. (If it is TP it may be any time, and the data will be drawn from a microsoft query report I have behind this one.)
My report needs to recognise that a claim is Comp, see that it has an estimation date and if so add an authorisation date 2 working days later, but only on that date, i.e. if a comp claim is estimated today the authorisation date of 4/10/08 should not appear until Tuesday.
i want to link my stock portfolio to a spreadsheet to make various calculations on historic performance.
my problem is that it is the same cell thats get updated whenever a change occurs, so that there is no history.
let's say i put the DDE-link in cell A1, i would like B1 to be filled with that value
and then when the value changes i would like B2 to be filled with the new value
and the next time B3, and so on.
I have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
I have my ms access and ms excel which is connected each other .
=> now in my ms access have a table name (tblMonth) has columns (year,MonthNum,StartDate,EndDate)
HERE: sample in january(1) and feb(2)
YearMonthNum StartDate EndDate
2014 130/12/201305/01/2014
2014 106/01/201412/01/2014
2014 1 13/01/201419/01/2014
2014 120/01/201426/01/2014
2014 127/01/201402/02/2014
2014 203/02/201409/02/2014
2014 2 10/02/2014 16/02/2014
2014 217/02/201423/02/2014
2014 224/02/201402/03/2014
=> and for my ms excel has its columns (year,month,date) in this if i input the date that is base in the database range from StartDate TO its EndDate will automatically put the month and year which base also in my database (year,MonthNum)
HERE:
A1 B1 C1
Year Month Date
A2=2014 B2=January C2=01/01/2014
I want to isolate text on either side of dashes ( " - " ) that occur either 2 or 3 times in a single cell.
Example:
Apples - Pears - 99 - 100+ years
Bananas - 56 - 57+ years
In particular, I would like to capture the "99 - 100+ years" and "56 - 57+ years" text on its own.
I tried this " =SUBSTITUTE(A1, "*- ", "", 1) but it doesn't work
I have a series of cells that can be auto filled based on the initial response in cell B8. For example, if cell B8=ABC, then cell B22 should be auto filled with 123, and cell B24 should be auto filled with Yes and cell B56 should be auto filled with the word Hosted.
View 5 Replies View RelatedI am trying to categorize a list of words based on the first letter.
For example:
[Code] ......
I want to see if the letter 'D' in 'Data' occurs in a range from A-G, if so display "A-G" in cell B,
else if the first letter occurs from H-M, display H-N in cell B
else if the first letter occurs from N-R, display N-R in cell B
else display S-Z in cell B.
Serial No Search E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4
E - Year Month Date
I need F column as Month Date Year Format
I have a data tabel in Sheet 1 & Sheet 2
In sheet 3 they are combined via reference in order for me to do calculation in the input - They are combined in sheet3 as the columns are not identical.
Because the data-tables will be updated via sharepoint, i have to link to cells beneath the data-tables as i expect these will be fill out when the tables update.
My problem is that when the tables update, i dont see the updates in my "combination" sheet. it appears the the data tables add a new line, which moves my cell reference 1 line down.
Ex: Had a reference in Sheet1'E3 - after the data table updates the reference is now Sheet1'E4.
I have created a macro that copies information from various cell on one sheet to another. When I run the macro the screen flickers about 5 times. Is there a way to stop.
View 5 Replies View RelatedI have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
View 2 Replies View Related
