+ Reply to Thread
Results 1 to 12 of 12

Unable to remove/change formatting and/or cell value.

  1. #1
    Registered User
    Join Date
    11-25-2014
    Location
    London, England
    MS-Off Ver
    Office 2013
    Posts
    5

    Unable to remove/change formatting and/or cell value.

    i have a spreadsheet with a column of dates. all of the dates display fine. However, some of the cells have a date that is not really a date. For example, it displays
    Please Login or Register  to view this content.
    , but if I clear formats, it displays
    Please Login or Register  to view this content.
    . Now, If I delete that text and key in "12/19/1994" it changes the format to date and displays as expected. However, if i go and clear formats again, I get the same
    Please Login or Register  to view this content.
    as before. Why are some of these cells doing this? When i view the formatting between the ones behaving appropriately and these, it is the same. How can i fix it?

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Unable to remove/change formatting and/or cell value.

    that's probably because XL "dates" are really numbers. they are usually the number of days since 1/1/1900. It makes it easier to do integer math on "dates". XL time is really the decimal portion of the date....so, .5 is really noon and .25 is 6AM....so, XL tries to help when you put a date like 11/25/2014, that's what it displays, but it's really a number...

    there is a ISNUMBER function you can use to determine that....HTH...

    also, remember, formatting only changes the way things are "displayed", it doesn't change the actual data....
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    11-25-2014
    Location
    London, England
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Unable to remove/change formatting and/or cell value.

    thanks judge. but that's my question, I need to change the actual data. It will not let me change that. I need the date to be a string literal date just like it is for the 200+ cells. I do not know why these few cells are different and I cannot change them.

    ETA: and to clarify, I don't mean it won't let me edit the cell. I can edit the data in the cell but as soon as i type something in the mm/dd/yyyy format it behaves as described above.

  4. #4
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Unable to remove/change formatting and/or cell value.

    oh, okay...

    try this

    '10/14/2014
    ^ that little thing is a single quote


    EDIT: wait - you want to convert what is already there to a string?...or you want to enter dates as strings?...sorry for the confusion

  5. #5
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Unable to remove/change formatting and/or cell value.

    if you want to convert what is already there try this....

    create a column next to the dates

    enter this formula (change the Cell)

    Please Login or Register  to view this content.
    copy it down the column
    copy & paste special to remove the formula
    delete the original column

  6. #6
    Registered User
    Join Date
    11-25-2014
    Location
    London, England
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Unable to remove/change formatting and/or cell value.

    I don't even know any more :-) I just want it to behave like the other dates in the spreadsheet. This spreadsheet is being read by a program, so I can't use the ' trick because I know the program won't parse it as a date. I just need it to display as
    Please Login or Register  to view this content.
    and if i clear formatting I need it to still say
    Please Login or Register  to view this content.

  7. #7
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Unable to remove/change formatting and/or cell value.

    can you try the conversion technique in post #5?....if not, can you upload a portion of the file with the company confidential stuff removed so I can take a look at it?....

  8. #8
    Registered User
    Join Date
    11-25-2014
    Location
    London, England
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Unable to remove/change formatting and/or cell value.

    i missed your post #5. I tried that and it worked like a charm. Judge, i needed a hero and you were it.

  9. #9
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Unable to remove/change formatting and/or cell value.

    glad I could help....sorry it took so long.....have a great day

  10. #10
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Unable to remove/change formatting and/or cell value.

    If this has been completed to your satisfaction, please set the thread to Solved...If you feel I deserve an increase in my reputation, feel free to click on the star in the lower left of the thread. Remember, someday when you help somebody they may add to your rep points, and it could be me...just a thought...

  11. #11
    Registered User
    Join Date
    11-25-2014
    Location
    London, England
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Unable to remove/change formatting and/or cell value.

    will do. thanks again

  12. #12
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Unable to remove/change formatting and/or cell value.

    my pleasure and thanks for the rep points...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Unable to change comma to . using formatting
    By Allgermanparts in forum Excel General
    Replies: 2
    Last Post: 10-24-2013, 08:46 AM
  2. [SOLVED] Unable to remove names on cell groups for use in drop down lists
    By Allsort in forum Excel General
    Replies: 1
    Last Post: 08-14-2013, 02:00 PM
  3. Unable to Change Cell Value in Excel Spreadsheet
    By cassien in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-16-2013, 11:37 AM
  4. Unable to remove the "space" at the end of text in a cell
    By jhiltabidel in forum Excel General
    Replies: 4
    Last Post: 03-27-2013, 02:15 PM
  5. [SOLVED] unable to change data in cell(s)
    By carrera in forum Excel General
    Replies: 0
    Last Post: 01-05-2006, 02:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1