#### jeffreyweir

##### Active Member

Scrub that. I'm getting confused by the US date format again.

- Thread starter Luke M
- Start date

Scrub that. I'm getting confused by the US date format again.

'=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"

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

However, you are bang on regarding the MOD(,4). Guess I overlooked some school day basics.

=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

Hi Sam ,

The 105 character one is great , thanks.

Narayan

The 105 character one is great , thanks.

Narayan