• 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


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

Formula Challenge 004 - Add and subtract days to pre-1900 dates

Okay, 150 characters, using non US date format:


Should work equally well for US if you change "DD/MM/YYYY" with "MM/DD/YYYY"
Hi Sam ,

Correct me if I am wrong.

The issue has nothing to do with regional settings ; the point is that your use of the INDIRECT function with the parameters "RC"&{2,50} relies on this formula being in row 1, or rather the same row as the data , since your data is assumed to be in A1 and B1.

Put the same formula in some other row , and see what happens.

And since your formula is using MOD(...,4) with a base of 2000 , it works with 0999 , since MOD(0999,4) gives 3 , which results in a year of 2003 , which is not a leap year , just as 0999.

Try this with dates in non-leap years , such as 03/01/1000 and -1 , 02/28/1000 and +1.

Narayan, the "RC" can be replaced by "R[&ROW(A1)&]C", so I don't think that's a cause to worry about.

However, you are bang on regarding the MOD(,4). Guess I overlooked some school day basics.
This 105 characters seem to be working, but not putting any money on it


To Narayan's point,

=TEXT(B2+(LEFT(A2,6)&RIGHT(A2,4)+2000),"mm/dd/")&TEXT(RIGHT(A2,4)+SUM(YEAR((LEFT(A2,6)&RIGHT(A2,4)+2000)+N(INDIRECT("R"&ROW(A2)&"C"&{2,50},FALSE)))*{1,-1}),"0000") 'also' seems to be working
Wow, several interesting takes on this!


Thanks for tackling the dd/mm/yyyy format. You were correct, I was not trying to force the formatting, just stating for clarification on interpretation of my data.