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

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

Okay, 150 characters, using non US date format:

'=LEFT(TEXT(DATEVALUE(LEFT($A2,6)&RIGHT($A2,4)+2000)+$B2,"DD/MM/YYYY"),6)&RIGHT(TEXT(DATEVALUE(LEFT($A2,6)&RIGHT($A2,4)+2000)+$B2,"DD/MM/YYYY"),4)-2000


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


=TEXT(B2+(LEFT(A2,6)&RIGHT(A2,4)+2000),"mm/dd/")&TEXT(YEAR(B2+(LEFT(A2,6)&RIGHT(A2,4)+2000))-2000,"0000")


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!

@Jeffrey

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.
 
You must log in or register to reply here.
Back
Top