Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I would like to lock a cell so that whatever data is input it ends up in the same format.

    We would like the cell to display data as dd/mm/yyy - I have selected this is the Format Cells option but if someone types in dd.mm.yy it accepts it and I would like it to change it automatically to dd/mm/yyy.

    Hope that makes sense?

    Can anyone offer me any advice on this please?

    Thanks

    Penny

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    You could run a macro on cell change that re-formats the cell? The problem is determining what the user meant to enter - dd/mm/yyyy, mm/dd/yyyy, yy.mm.dd, etc

    cheers, Paul

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To lock a cell so the format can not be changed, requires that the cell be locked so the value can not be changed either.

    But the problem you mention is not formatting change at all. The format remains for dates the format you chose. The problem is that excel does not recognize the entry using periods as a date but text. Entries that excel can convert to dates it will and then will have the format you have on the cell. Things that do not look like dates will be text.

    You could create a macro to convert text or numbers to dates. Some example code can be found at MS MVP Chip Pearson's website: http://www.cpearson.com/excel/DateTimeEntry.htm

    Steve

  4. #4
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your replies Uranium and Bronze Lounger

    I'm wanting to set the spreadsheet up so that any text input into the spreadsheet by any user is automatically altered to our requirements, rather than a macro having to be run to amend after the event.

    Going on what you say - I'm not sure this is achieveable

    Thanks

    Penny

  5. #5
    5 Star Lounger
    Join Date
    Dec 2003
    Location
    Burrton, KS, USA
    Posts
    833
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Going on what you say - I'm not sure this is achieveable

    I think this is correct. If you use code or a macro to manipulate the text in the cell and create a date from it, I doubt that the end result will be accurate.

    If someone types a recognizable date it will be switched to your date format, but if they type the date as text, sometimes with the year first (yy.mm.dd or yyyy.mm.dd or yy.m.d, etc.) and sometimes with the month first (mm.dd.yy or mm.dd.yyyy or m.d.yy, etc.) you will have a very hard time converting it to the correct date.....

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Going on what you say - I'm not sure this is achieveable
    I would say it is achievable via training. Train the users to enter the dates properly and they will be converted properly.

    If your users can not be trained to consistently enter the dates into one cell, you could have them use multiple cells. Have pulldowns for the year, month, and (based on year and month) the number of days in that month. Then from these numbers you could calculate the day...

    Steve

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Penny,

    Have you looked at Data --> Validation? You can use this to force date input while providing a prompt for the user to show the correct format. You can select a whole range of cells then enter the validation parameters and it will apply to all. If you later insert a row within the range the validation applies to it will also apply to the inserted row.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    FWIW, I agree with RG. By using validation, when they roll over the cell, a note will pop up and in that note you can show them the format that they need to use.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  9. #9
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Retired Geek!!! That's fab! I've done it and I think that will provide the 'training' that others refer to!

    Marvelous...

    Thanks once again to this forum!!

    Penny

  10. #10
    New Lounger
    Join Date
    Jan 2010
    Location
    Melbourne, Vic, Australia
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey There,

    Data Validation is great when you want to provide some easy guidence to users, and has some useful ways of setting rules for what data is acceptable.

    Just keep in mind that Data Validation doesn't prevent people copying and pasting data that isn't valid onto those cells.

    Bit of a flaw in my opinion.

    Love these forums,

    Paul.

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Raleigh, NC US
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul:

    I have had the same copy issue that you described when using Validity to set data entry into a cell. Someone can copy a cell or cells and overwrite the Validity check. Does anyone out there know of some way to allow pasting of cells, but enforce Validity checks? This is a legitimate issue when using excel for consistent information gathering within organizations. Excel is easy and most individuals can use it on a basic level, but they do not understand the product to a depth to understand that the paste function can get them into trouble.

  12. #12
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    No input validation can tell if "08/09/10" is a correct date. Is it DD/MM/YY or MM/DD/YY or even YY/MM/DD? Therefore ALWAYS ask for day, month, year in separate cells, then validate each field and build the date value in a locked cell. Use another locked cell to display a warning if invalid data is entered. Remember your validation depends on the context - 1980 is a valid date of birth but an invalid date of death.

    Another thing to bear in mind if your spreadsheet is shared between users is that many users have "incorrect" localisation for the default date format. Always specify a format when converting dates to strings.

  13. #13
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    As for enforcing validity checks, if your check is done using a list in the spreadsheet use a value returned from a VLOOKUP on the list rather than the value entered. If the user circumvented your validation the result will be #N/A

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How about a pop-up calendar. The attached spreadsheet describes how to do this in Excel 2003. Not sure about 2007. In the code, you have to format the column to the date format you want.
    Attached Files Attached Files

  15. #15
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Just a tip for data entry Ctrl + ; enters the current date.
    This is often useful to "orient" oneself as to the default date format used by the machine.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

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