Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-14-2019, 12:57 PM
cmersho cmersho is offline Auto insert dates for a complete year Windows 10 Auto insert dates for a complete year Office 2016
Novice
Auto insert dates for a complete year
 
Join Date: Jan 2019
Location: Indiana
Posts: 1
cmersho is on a distinguished road
Wink 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.
Reply With Quote
  #2  
Old 01-15-2019, 08:45 AM
ArviLaanemets ArviLaanemets is offline Auto insert dates for a complete year Windows 8 Auto insert dates for a complete year Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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)
and copy it down for 366 rows.

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).
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto insert dates for a complete year 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
Auto insert dates for a complete year How to insert a year-only SaveDate field David Ziegler Word 2 04-10-2014 07:38 PM
Auto insert dates for a complete year Auto insert current month's name and current year Styler001 Word 4 01-25-2010 06:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft