1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Year Formula Error

Discussion in 'Ask an Excel Question' started by Callum Mc Adam, Apr 13, 2018.

  1. Callum Mc Adam

    Callum Mc Adam New Member

    Messages:
    7
    HI Excel Ninja's

    Hope you can help me, i pull reports from our system and each line date has the full time included. I usually used the =YEAR(B1) Formula to only include the year and that has worked fine. i recently moved to the latest version of office (2016 - on Office 365 E3 Plan) and now the =YEAR() Formula no longer works.

    Attached is an example of the Date Format and Year Formula.
    I have tried Changing the Column Format but that doesn't seem to work.

    I would be eternally grateful for any assistance.
    thank you

    Callum Mc Adam
    South Africa

    Attached Files:

  2. Ramesh

    Ramesh Active Member

    Messages:
    173
    Hey Adam,

    Not sure why and how.. when I opened your worksheet on my computer and run the formula it evaluated fine.

    Please check if the Formulas => Calc mode is set to auto on you Excel application..

    I'm here by sharing the file, where the formula got evaluated correct.

    Thanks,
    Ram

    Attached Files:

  3. Tim326515465

    Tim326515465 New Member

    Messages:
    10
    Try highlighting column A change it from general to long date (your current date format will be "dd mmm yyyy h:mm:ss AM/PM" which you can create under custom) and then try your formula again
    Last edited: Apr 13, 2018
  4. Callum Mc Adam

    Callum Mc Adam New Member

    Messages:
    7
    HI Ram,

    Thank you for your reply, I have checked this and it is set to Auto. Not sure why It's not working?

    Will keep checking
    thank you
    Callum
  5. Callum Mc Adam

    Callum Mc Adam New Member

    Messages:
    7
    @Tim326515465,
    Thank you for your reply. I have also tried that and doesn't seem to work either.

    Kind Regards
    Callum
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    Hi Callum,
    Press F2 in each of the cells followed by enter. If formula works, then there is something wrong with the cell formats. It could be as text or data format in a foreign regional format.
  7. Callum Mc Adam

    Callum Mc Adam New Member

    Messages:
    7
    @GraH - Guido
    Thank you for your reply. The Formula works however i get the #VALUE! Error
  8. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    You pressed F2+enter in the column A and still it returns an error?

    When I open your workbook I see this (the error is quit explicit):
    upload_2018-4-13_16-36-32.png

    After entering the cell A2 with F2 and reconfirming it with enter, I get:
    upload_2018-4-13_16-37-51.png
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    The Values in Column A are text not Dates

    Select any of them and press the upload_2018-4-13_22-39-17.png button
    A true date would change to a Number like 42186.5
  10. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    Just saying , it seems I already suggested it in #6. :(
  11. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,913
    When I tried to open the file I noticed the same behavior.

    Obs #1] The error that excel quotes on the cell is:
    A value used in the formula is of wrong data type.

    Obs #2] If I write the same formula in adjacent cell then it works without error. So I have a spreadsheet where I have two different results from identical formula which is reading data from the same cell.

    Obs #3] If I perform F2 & ENTER on
    A2: Cell data format changes from "General" to Default Date
    B2: Formula starts working on irrespective of error Excel initially showed in Obs #1.

    Obs #4] Sheet calculate has no effect and so has opening and closing of the workbook.

    Obs #5] However VBA based
    Code (vb):
    Application.CalculateFull
    was able to force all formulas to calculate and then work as desired.

    I am confused by this behavior to say the least as I could not put finger on the exact cause of it except a faint feeling that Excel somehow fails to calculate the results for some unknown reasons which get fixed up when we force it to calculate either by
    a. Fresh inserted formulas
    b. F2 on old cell
    c. VBA forced recalculation.

    @Callum Mc Adam Can you open the attached file and see what results you get since you've mentioned F2 doesn't work at your end?

    Attached Files:

    Last edited: Apr 15, 2018
    Callum Mc Adam and GraH - Guido like this.
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Shrivallabha ,

    Even CTRL ALT F9 works.

    Narayan
    Callum Mc Adam likes this.
  13. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    Wow, you went an extra mile to figure out what goes wrong shrivallabha. Now what I found to be "normal", suddenly is disturbing like you say. Thanks for sharing your findings.
    Going to find out what CTRL ALT F9 does, NARAYAN.
    Callum Mc Adam likes this.
  14. Callum Mc Adam

    Callum Mc Adam New Member

    Messages:
    7
    @GraH - Guido , @NARAYANK991 ,@shrivallabha ,
    Thank you All for looking into it. I still haven't come right unfortunately with changing the format. as soon as i Remove the time from the cell then my formula works. I am going to use a simple Left and Right formula to retrieve the year. =RIGHT(LEFT(A2,11),4).
  15. Callum Mc Adam

    Callum Mc Adam New Member

    Messages:
    7
    H
    Hi @Hui,
    Unfortunately this did not work either.
  16. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    795
    It just reconfirms the observations made before (specially shrivallabha's).
    Disturbingly, 3 ninja's and 2 "normal" user all say the same and can make it work at their end. Beats me why you need to apply another workaround before the formula "awakes". What is your local date and time-format setting? Where does the data come from? Do you have the same issue/behavior when using a new workbook?

    Note your formula left and right is only working because Excel sees the data as text... If for any reason, a date would be recognized as date, that formula will stop working correctly. If I were you, I would not feel 100% comfortable using it. The root cause should be found and resolved. Otherwise the problem remains and will come back to bite, where you do not want to be bitten.
    shrivallabha likes this.
  17. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,913
    Try below and share results.
    - Select cell A2 (or any cell containing dates as string)
    - Press F2 followed by ENTER
  18. Callum Mc Adam

    Callum Mc Adam New Member

    Messages:
    7
    HI Grah
    I recently moved to a newer Office version. The company I work for upgraded my HDD to an SSD. The Data i pull from our System is a Sellout for the month with all our customers. I looked at a previous sellout sheet i exported to CSV and noticed the Date and Time is in a different format. I am taking it up with my IT Department to find out why the System is exporting the date all of a sudden in a different format.
  19. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,913
    Please check with IT if there's any change in the system's regional settings.

Share This Page