Results 1 to 7 of 7

Thread: Month Formula

  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I created a Excel Spreadsheet in (2003) a while back which worked beautifully, however, now that I have transferred it to (2007) I keep getting a #VALUE! from Row 556 on. I am attaching a spreadsheet with the copied formula which works fine, and the one that does not. I have not done anything to the spreadsheet. I simply opened and started typing. The purpose of the column for the month is that I want to be able to sort all the January, February, etc. whenever I want without having to, after typing the date, having to make sure that I type the corresponding month.

    I did clear the contents of the formula and then copied a good formula into it. Same result, #VALUE!.

    I chose a random cell after Row 556 and the result is the same #VALUE! So, all the formulas that were copied to Row 556 on has this particular "whatever error". Obviously something is in there, however, I have been unable to see it. Can somebody check the enclosed spreadsheet and see if there is something in the cell that is causing the problem. I cannot send the whole spreadsheet because it has sensitive information in it. But the spreadsheet consists of first name, last name, address, etc., including the birth date of the person.

    It does not matter if I go back to the machine that has Vista (old version) and the new machine that has Vista 7 (newer version). It happens on both machines.

    I just noticed that the dates in the formulas display differently, 3/14/14 and 3/14/1914, however, when I check the cell format both that the same format chosen, Date *3/14/2001.

    Thank you in advance for your help.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    I converted your file to a CSV and it appears there are some rogue characters in the data string

    Code:
    3/?5/?58,#VALUE!,,,,,,,,,,,,,, 
    20/09/1941,9,,,,,,,,,,,,,,
    Note the question marks, so it looks like there has been some data corruption.
    Jerry

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Hong Kong, China
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The MS Excel Help file stated that if the date parameter is entered as string, there may be problem for the MONTH() function. Try converting your dates into formulas "=DATE(year,month,day)"
    There may be some problem during conversion from xls to xlsx.
    Maverick Tse

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Ypsilanti, Michigan, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try formating the date column as mm/dd/yyyy. That way all entries into those cells display the date and the Month() function recognizes the numeric month.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The format seems to be the problem. I opened the workbook and re-entered the date and the formula returned a 3.
    I am using 2007.
    The original date may have been entered as text.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Formatting will not help using the various formulas, I have tried before my post, I have a feeling some of this data may have been imported
    Jerry

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by stans View Post
    The original date may have been entered as text.
    That is indeed the problem. Cell A1 contains text that looks like a date, cell A3 contains a real date. You can see this by setting the horizontal alignment to General. A1 will remain left-aligned while A3 will become right-aligned (the default for dates).

    But the usual methods for converting text to dates don't work - there appear to be hidden characters in the text string, and Jezza's experiment confirms that.

Posting Permissions

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