First post, so hello. I'm a proper noob when it comes to excel. I can do formulas and create accounts spreadsheets (pretty basic) but need some help with formatting.
I have an excel template with the following column headers:
prop-ref, title, forename, surname, address1, letter date, visit 1, visit 2, date of entry,
loc. of valve, valve type, mixed temp, hot temp, hot pressure, cold temp, cold pressure, flow rate, cw fail test, initials, next service due, comments.
I need to format the latter date, 1st visit, 2nd visit, date of entry, and next service due columns to only accept a date input. When i try this i just end up with either dates that are miles out or just show as # symbols (no matter the width of the column). It's really bugging me.
I also need to ensure that the date entered in the visit 2 column is at least 7 days in advance of whatever date is entered in the visit 1 column. But i reckon I could do this with a simple if formula????
There is much more but this would be great to start with. If any one has any tips then please let me have them.
Hello, and welcome to the forum,
So to format as a date, right click the cell, choose "Format Cells" and click the number tab. There are a variety of date formats shown in the "Date" section at left. If it is still giving you issues, this article on my site may help: http://www.excelguru.ca/node/100
With regards to forcing the user to enter valid dates, you actually want data validation. To force a user to enter a data in Excel 2007:
This could also work for your question about at least 7 days by plugging your end date as =A1-7
- Data tab
- Data Validation
- Allow Date
- I usually enter something like =TODAY() for the start date
- End date can be left blank or be a formula
Hope this helps,
Ken Puls, FCPA, FCMA, MS MVP (Excel)
Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.