Formula to grab last two digits of year not working for some users

[ExceL0ver]

Hi all,

I have the formula below which is used to generate a project code, based on data in columns B, C, and E. Column E contains dates in the format dd/mm/yyyy, and the formula is meant to grab just the last two digits of the year. The formula works perfectly fine for me, however when other users use the same spreadsheet, the format changes so rather than having the two digits of the year, the project code has "YY" in place of this. Then when I click on the cell containing the formula and press enter, the format then changes back to the desired format. Could anyone suggest why this is happening, and what I should do to fix it?

=IF(OR(ISBLANK(B10),ISBLANK(C10),ISBLANK(E10)),"",B10&"-"&TEXT(E10,"YY")&"EN"&(TEXT(Y10,"000")))

Thanks

 

[Georgiboy]

Perhaps you could try YEAR rather than TEXT:

RIGHT(YEAR(E10),2)

 

[Fluff]

Could anyone suggest why this is happening

Sounds like they are using a foreign language version of Excel.
For instance, instead of dd-mm-yyyy the French would use jj-mm-aaaa.

 

[ExceL0ver]

Sounds like they are using a foreign language version of Excel.
For instance, instead of dd-mm-yyyy the French would use jj-mm-aaaa.

Thanks Fluff, I think this may be why because they are based in another country. Is there anything I can do to ensure compatibility for all users?

 

[Fluff]

Have you tried what Georgiboy suggested?

 

[ExceL0ver]

Perhaps you could try YEAR rather than TEXT:

RIGHT(YEAR(E10),2)

Thanks, I'll give this a shot and see if it works!

 

[ExceL0ver]

Perhaps you could try YEAR rather than TEXT:

RIGHT(YEAR(E10),2)

Thanks Georgiboy, this works!

 

[Georgiboy]

You are welcome, glad you have it sorted