Printable View
Hey Howdy team, hope you are all well.
I am starting to wonder if this is even possible, but I would like it if in this one spreadsheet I could put a date in one column, and have an 11-month later date populate the nextcolumn, and a 12-month later date populate another.
In other words
column B column C column F
I enter populates populates
04/01/2003 03/01/2004 04/01/2004
This HAS to be possible. If anyone can help, I would love you forever..
Thanks!
Reggie
From typing auto fill date in the help for Excel xp
Quote:
Show All
Enter data in worksheet cells
Enter numbers, text, a date, or a time
Click the cell where you want to enter data.
Type the data and press ENTER or TAB.
Numbers and text in a list
Enter data in a cell in the first column, and then press TAB to move to the next cell.
At the end of the row, press ENTER to move to the beginning of the next row.
If the cell at the beginning of the next row doesn't become active, click Options on the Tools menu, and then click the Edit tab. Under Settings, select the Move selection after Enter check box, and then click Down in the Direction box.
Dates Use a slash or a hyphen to separate the parts of a date; for example, type 9/5/2002 or 5-Sep-2002. To enter today's date, press CTRL+; (semicolon).
Times To enter a time based on the 12-hour clock, type a space and then a or p after the time; for example, 9:00 p. Otherwise, Microsoft Excel enters the time as AM. To enter the current time, press CTRL+SHIFT+: (colon).
Enter numbers with a fixed number of decimal places or trailing zeros
On the Tools menu, click Options, and then click the Edit tab.
Select the Fixed decimal check box.
In the Places box, enter a positive number of digits to the right of the decimal point or a negative number for digits to the left of the decimal point.
For example, if you enter 3 in the Places box and then type 2834 in the cell, the value will be 2.834. If you enter -3 in the Places box and then type 283, the value will be 283000.
Data you entered before selecting the Fixed decimal option is not affected.
Tip
To temporarily override the Fixed decimal option, type a decimal point when you enter the number.
Enter the same data into several cells at once
Select the cells where you want to enter data. The cells do not have to be adjacent.
Type the data and press CTRL+ENTER.
Enter or edit the same data on multiple worksheets
When you select a group of sheets and then change data on one of them, the same changes are applied to all the selected sheets. Data may be replaced in the process.
Select the worksheets where you want to enter data.
How?
When you enter or change data, the changes affect all selected sheets. These changes may replace data on the active sheet and other selected sheets.
To select Do this
A single sheet Click the sheet tab.
If you don't see the tab you want, click the tab scrolling buttons to display the tab, and then click the tab.
Two or more adjacent sheets Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.
Two or more nonadjacent sheets Click the tab for the first sheet, and then hold down CTRL and click the tabs for the other sheets.
All sheets in a workbook Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.
Note If sheet tabs have been color-coded, the sheet tab name will be underlined in a user-specified color when selected. If the sheet tab is displayed with a background color, the sheet has not been selected.
Cancel a selection of multiple sheets
To cancel a selection of multiple sheets in a workbook, click any unselected sheet.
If no unselected sheet is visible, right-click the tab of a selected sheet. Then click Ungroup Sheets on the shortcut menu.
Select the cell or cell range where you want to enter data.
Type or edit the data in the first selected cell.
Press ENTER or TAB.
Note To cancel a selection of multiple sheets, click any unselected sheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.
Automatically fill in repeated entries in a column
If the first few characters you type in a cell match an existing entry in that column, Microsoft Excel fills in the remaining characters for you. Excel completes only those entries that contain text or a combination of text and numbers; entries that contain only numbers, dates, or times are not completed.
To accept the proposed entry, press ENTER. The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entries.
To replace the automatically entered characters, continue typing.
To delete the automatically entered characters, press BACKSPACE.
To select from a list of entries already in the column, right-click the cell, and then click Pick from List on the shortcut menu.
Fill in a series of numbers, dates, or other items
Select the first cell in the range you want to fill.
Enter the starting value for the series.
Enter a value in the next cell to establish a pattern.
How?
If you want the series 2, 3, 4, 5..., enter 2 and 3 in the first two cells. If you want the series 2, 4, 6, 8..., enter 2 and 4. If you want the series 2, 2, 2, 2..., you can leave the second cell blank.
To specify the type of series, use the right mouse button to drag the fill handle over the range, and then click the appropriate command on the shortcut menu. For example, if the starting value is the date JAN-2002, click Fill Months for the series FEB-2002, MAR-2002, and so on; or click Fill Years for the series JAN-2003, JAN-2004, and so on.
To manually control how the series is created, or use the keyboard to fill in a series, use the Series command on the shortcut menu.
Select the cell or cells that contain the starting values.
Drag the fill handle over the range you want to fill.
To fill in increasing order, drag down or to the right.
To fill in decreasing order, drag up or to the left.
Enter the same data in other worksheets
If you've already entered data on one worksheet, you can quickly copy the data to corresponding cells on other sheets.
Select the sheet that contains the data and the sheets to which you want to copy the data.
Select the cells that contain the data you want to copy.
On the Edit menu, point to Fill, and then click Across Worksheets.
Let's suppose that you enter the date 04/01/2003 in cell B4. To automatically calculate dates, typically the formula is in the form C4=B4 + (number of days). This means that you would need to calculate the number of days in 11 months. Of course, some months are longer than other months.
Same for adding one year to the date. You can add 365 to your value of B4 but again, some years are longer than others...i.e. leap year.
So, I don't have an exact formula for you. If you do find a good formula, please post it here.
=A1 + "12/01/2000[The date you want to add]" - "1/01/2000[To manipulate the data for what day and year you want]"
This formula is kinda tricky (You will see what I mean). You have to play around with it to get the feel so you can get the values you want. Make sure you format the field as 00/00/0000 in order to see you have the correct year. To take away a few days just minus as an integer however many days. If you have any questions let me know. Hope this helps.
piece of cake
a1 4/2/03
b1 =DATE(YEAR(a1),MONTH(a1)+11,DAY(a1))
c1 =DATE(YEAR(a1),MONTH(a1)+12,DAY(a1))
edit
didn't it occur to anyone to open up excel help and type "add months"???? :D :cool:
Yup, I pasted the help file as a hint...Quote:
Originally posted by kato2274
piece of cake
a1 4/2/03
b1 =DATE(YEAR(a1),MONTH(a1)+11,DAY(a1))
c1 =DATE(YEAR(a1),MONTH(a1)+12,DAY(a1))
edit
didn't it occur to anyone to open up excel help and type "add months"???? :D :cool:
kato's formula works...i just tried it.
As for NooNoo's help file, I couldn't make sense out of it. But hey, that's just me. Also, i'm running office97...it must have diff help available than office2000.
Thats certainly easier...