#1
|
|||
|
|||
Auto insert dates for a complete year
Hello, Using office 365. I have a form in excel that contains 14 date fields. Currently I have to go into the fields and change the dates manually than print the form for each month. This process takes a long time to do the entire year. Is it possible to add a start date like 1/1/19 on another sheet, then auto populate the date fields on my form and print until it reaches 365 days? Or do I need to make 24 sheets and have them auto add the dates? Either way can someone tell me how? I'm quit new to using formulas and excel, so any help would be appreciated. Thanks in advance to all. |
#2
|
|||
|
|||
It is easy to write a formula which displays a certain date into certain row. Like into some Setup sheet you enter the year number and define the cell as Name, e.g. MyYear. Now on your data sheet into cell A2 you enter the formula
Code:
=IF(DATE(MyYear,1,1) + ROW() -2>TODAY(),"",DATE(MyYear,1,1) + ROW() -2) The problem with calculated dates is, that when you need to sort this table, or to add new rows somewhere, or to delete some rows, then data you entered into other columns are moved into new rows, but dates are remaining where they were before - and your data will be attached to wrong dates. Workarounds: 1. E.g. into cell A2 enter date for January 1st, and into cell A3 enter January 2nd. Select both cells, with mouse activate vertical cross in right lower corner of selection, and drag mouse down - every new record will get a date next to previous. NB! those are real entries, not formulas! 2. Take empty workbook form with formulas to calculate dates into proper cells (dates for whole year). Copy the whole range with data, and use PasteSpecial>Values to replace all formulas with values. Save the workbook form with different name (so you can use the same form for next year again). 3. Write a procedure which fills the table with dates in proper cells. An advanced option is you have a specified workbook, where you enter year number, and click on button - and the procedure called from button click event creates a new workbook properly formatted and prefilled with dates (and other fixed data). |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto insert year only | fithawk | Word | 11 | 12-21-2016 02:50 AM |
Sorting dates without year | bankerbuddy | Excel | 2 | 06-18-2015 12:47 PM |
Once you have a % complete does it always lock in the dates? | pm37064 | Project | 1 | 05-27-2014 07:38 AM |
How to insert a year-only SaveDate field | David Ziegler | Word | 2 | 04-10-2014 07:38 PM |
Auto insert current month's name and current year | Styler001 | Word | 4 | 01-25-2010 06:40 PM |