I have attached small file to illustrate what I have encountered. When using today as ordinal day (334) and formulas to convert to calendar format, I get 11/30/2014 (as it should be). However, when I use a custom format [dd] to only capture the numerical day, what is returned in cell [C3] is 29. That is one off. Does anyone know why this happens? Thanks!
Because you've subtracted the year and 29 refers to the 334th day of 1900, which must have been 29th November... Reformat to short date and see...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
That's due to the fact that when you get the result of the formula
=TODAY()-DATE(YEAR(TODAY()),1,0)
it equals 334 or 29/11/1900 in date format.
1900 is a leap year as per Excel. As such, when you calculate the day based on this result it will consider 29 days in Feb and will give you a day short of what your requirement is unless you use it in a leap year too. Your results prior to feb 29 will all be correct whereas all results post that will be off by a day.
Hope this helps
Last edited by Ace_XL; 11-30-2014 at 11:50 AM.
Life's a spreadsheet, Excel!
Say thanks, Click *
But only in the bizarre world of Bill Gates' imagination was 1900 a leap year
It appears if I use Row 10 and replace the existing formula in [B2] with [B2] = [C10], then I get 30 as the answer in [B3]. Somehow in Row 10 that 1900 year hidden reference gets purged from the formula [B2]=TODAY()-DATE(YEAR(TODAY()),1,0). I don't think that's what you meant by the "reformat to short date", so I am not doing something right in a specific cell in Row 3.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks