#### jeffreyweir

##### Active Member

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

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- 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