2

I'm working in excel online. I already checked calculation issues. And for some reason the following formula only works when I go to the cell and hit enter:

=text(A1;"mmm-yy")

Expected output : Jan-21
Actual output: Jan-yy

If I go to the cell and hit enter I get the correct value, but if I open the file again sometimes is correct and sometimes not.

ZygD
  • 22,092
  • 39
  • 79
  • 102
erosero
  • 21
  • 2
  • 2
    Your semicolon gives away that en-us or en-uk might not be your locale settings. So probably "yy" is just treated as a string. It has *no* meaning for your locale. Could it be that it only works at a pc with the right language? – JvdV Feb 03 '21 at 20:39
  • I totally agree with JvdV. In "Format Cells" dialog under Custom you can see what date format strings you can have. In my current locale, I would have "MMM-ÅÅ" for "Jan-21". No regular "yy". – ZygD Feb 03 '21 at 20:44
  • I encountered the same with a vba for a British colleague of mine (I'm located in Holland). Use the formatting, instead of the function for a universal result. – P.b Feb 03 '21 at 20:49
  • I checked and the format is correct "yy". And if it was a format problem why would it work when you hit enter? @ZygD – erosero Feb 03 '21 at 20:52

1 Answers1

2

As mentioned in my comment; it doesn't look like your locale settings recognizes the "yy". I'd tackle that problem by using an actual numberformat on the cell itself which (should) keep working when sharing your file across different locales. It does also keep the numeric value of the cell's content intact.

If however you need to use this string somehow, you could use:

=REPLACE(TEXT(F12,"mmm-e"),5,2,)

Where "e" is the international placeholder1 for "yyyy". After that, we simply cut out the part of the resulting string we want to ignore.

1: I am yet to find actual documentation on this feature. It seems undocumented.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    That's a pretty clever solution thanks, it works just as my formula in the sense it gives the same value, the question now is if I will have the same issue. I'm gonna try to keep track of those cells and see if they fail. Thanks again – erosero Feb 03 '21 at 21:30