I am using Office 365 with Windows 7 Pro. In Excel, I am using 4 digit years, years greater than 2030 calculate wrong. I understand the 2 digit year problem and have extended the Windows date to 2090, but still having the problem. I must be missing something. Any suggestions?
Can you give an example of your problem?
Entia non sunt multiplicanda sine necessitate
Formula. It works as long as the year is 2029 or less.
=TEXT(L17,"yyyy")&TEXT((L17-DATEVALUE("1/1/"&TEXT(L17,"yy"))+1),"000")
L17 is 12/31/2030
Result
203036890
Should be 2030365
confirm about what is format of "L17"
If you attach sample file its more helpful of both.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
I do not know how to attach sample file, but here is a portion of the K and L columns.
The K column (Julian) if formatted General and the Year column is formatted Date.
It is part of a retirement projection and you can see at the year 2030 how it is inaccurate.
By the way, it is the same if you enter each date individually or fill down.
Julian Year
2017365 12/31/2017
2018365 12/31/2018
2019365 12/31/2019
2020366 12/31/2020
2021365 12/31/2021
2022365 12/31/2022
2023365 12/31/2023
2024366 12/31/2024
2025365 12/31/2025
2026365 12/31/2026
2027365 12/31/2027
2028366 12/31/2028
2029365 12/31/2029
203036890 12/31/2030
203136890 12/31/2031
203236891 12/31/2032
203336890 12/31/2033
Here is formula in year column
=DATE(YEAR(L16)+1,MONTH(L16),DAY(L16))
Last edited by omayes; 03-26-2017 at 04:42 PM. Reason: addition
What is 203036890?203036890 12/31/2030
That's the problem. I cannot find why the 12/31/2029 Julian is correct and the 12/31/2030 and later year are returning wrong answers. The formula in each of the Julian cells are the same except for the reference cell. (see above)
B2 and down: =DATE(LEFT(A2, 4), 1, 0) + RIGHT(A2, 3) C2 and down: =1000*YEAR(B2) + B2 - DATE(YEAR(B2), 1, 0)
Refer below link hope this will helpful.
https://support.office.com/en-gb/art...#_Toc298144367
avk - thanks for the link. It is the resource I have been using the last two weeks trying to resolve this, bu tno luck.
shg - your column c formula works to get the correct number. However, a great big BUT. The next three columns LOOKUP values from three different sheets in the same spread sheet. These columns immediately change to #N/A and I can find no reason for this. The formula returns 2030365 just as it should, but cannot find the lookup for 2030365 on the other sheets (even though nothing changed on these sheets). I will continue to try and find out why.
I think shg has given you a better alternative but the reason your formula doesn't work is because of the "yy" part highlighted - this only takes the 30 from 2030 and then gives the date "1/1/31" - that date is interpreted by DATEVALUE as 1931, not 2031.Originally Posted by omayes
Windows has a setting (accessible through the control panel) which determines how two digit years are interpreted, typically that's in the range 1930 to 2029, so once you reach that cut-off point your formula doesn't work.
Three options
1) Use "yyyy" in place of "yy" in your current formula
2) Change the windows setting to re-interpret two digit years
3) Use shg's alternative formula
3) is recommended
Although, noting your previous comment you need the value as text - try this version:
=YEAR(B2)&(B2-DATE(YEAR(B2),1,0))
Last edited by daddylonglegs; 03-26-2017 at 05:45 PM.
Audere est facere
daddylonglegs - thank you, thank you. I added the 2 y's in the first cell and copied down and all is well. Problem solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks