Results 1 to 2 of 2

Thread: Help with formatting cells in excel 2007.

  1. #1

    Help with formatting cells in excel 2007.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    TIA

    H

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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:
    • 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
    This could also work for your question about at least 7 days by plugging your end date as =A1-7

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •