• 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...

  • 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

Callum Mc Adam

New Member
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
 

Attachments

  • Book2.xlsx
    8.6 KB · Views: 13
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
 

Attachments

  • Book2.xlsx
    8.6 KB · Views: 2
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

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:
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
 
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.
 
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
 
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
 
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):
View attachment 51444

After entering the cell A2 with F2 and reconfirming it with enter, I get:
View attachment 51445
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:
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?
 

Attachments

  • Book2 (1).xlsx
    8.9 KB · Views: 1
Last edited:
@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).
 
@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).
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.
 
@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).
Try below and share results.
- Select cell A2 (or any cell containing dates as string)
- Press F2 followed by ENTER
 
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.

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.
 
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.
Please check with IT if there's any change in the system's regional settings.
 
Back
Top