+ Reply to Thread
Results 1 to 12 of 12

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

  1. 11-25-2014, 06:42 PM #1
    johnnynumber5
    johnnynumber5 is offline
    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?
    Reply With QuoteRegister To Reply
  2. 11-25-2014, 06:51 PM #2
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    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....
    Reply With QuoteRegister To Reply
  3. 11-26-2014, 11:10 AM #3
    johnnynumber5
    johnnynumber5 is offline
    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.
    Reply With QuoteRegister To Reply
  4. 11-26-2014, 11:55 AM #4
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    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
    Reply With QuoteRegister To Reply
  5. 11-26-2014, 12:00 PM #5
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    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
    Reply With QuoteRegister To Reply
  6. 11-26-2014, 12:05 PM #6
    johnnynumber5
    johnnynumber5 is offline
    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.
    Reply With QuoteRegister To Reply
  7. 11-26-2014, 12:07 PM #7
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    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?....
    Reply With QuoteRegister To Reply
  8. 11-26-2014, 12:32 PM #8
    johnnynumber5
    johnnynumber5 is offline
    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.
    Reply With QuoteRegister To Reply
  9. 11-26-2014, 12:39 PM #9
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

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

    glad I could help....sorry it took so long.....have a great day
    Reply With QuoteRegister To Reply
  10. 11-26-2014, 12:40 PM #10
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    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...
    Reply With QuoteRegister To Reply
  11. 11-26-2014, 01:24 PM #11
    johnnynumber5
    johnnynumber5 is offline
    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
    Reply With QuoteRegister To Reply
  12. 11-26-2014, 01:55 PM #12
    judgeh59
    judgeh59 is offline
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

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

    my pleasure and thanks for the rep points...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1