6

I found an excel formula that transforms calendar dates into Chinese lunar dates:

=TEXT(A1,"[$-130000]d/m/yyyy")

Say A1 is 

08/12/2018

The output of the formula is 

2/11/2018

How does this work? What is $? What is $-130000? The MS's help article on TEXT does not mention this.

What formula can I use to do the reverse calculation, i.e. from lunar calendar to solar calendar? $+130000 does not work.

Update

I just came across this answer on SO, which says [$-xxxx] is some locale code, which makes perfect sense to me. However, I cannot find any source says what locale 130000 represents.

Anthony
  • 3,595
  • 2
  • 29
  • 38
  • 2
    The place where it should be explained is https://support.office.com/en-us/article/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5, but it is not there either. If you put `[$-130000]d/m/yyyy` in the cell's custom format box, Excel transforms it to `[$-,13]d/m/yyyy`, and that indeed gives the same result as the original. – GSerg Dec 27 '18 at 16:59
  • @GSerg looks like `$-` indicates some sort of encoding/decoding. – Anthony Dec 27 '18 at 17:30
  • Also Excel converts `[$-13F800]d/m/yyyy` to `[$-x-sysdate,13]d/m/yyyy`, and `[$-13F400]d/m/yyyy` to `[$-x-systime,13]d/m/yyyy`... – GSerg Dec 27 '18 at 21:08
  • From https://www.myonlinetraininghub.com/excel-dates-displayed-in-different-languages we see that Excel can display dates in "different languages" where language ID is taken from "[$-ID]..." – Александр Ермолин Dec 29 '18 at 08:41
  • 1
    @АлександрЕрмолин yes, but what locale or language ID does 130000 represents? I can't find any reference. – Anthony Dec 29 '18 at 14:36
  • Really I could not clarify full list of possible identifiers, if it is missing in official sources. Let MS developers answer... But the question was about the **$ sign meaning**. – Александр Ермолин Dec 29 '18 at 15:33
  • @АлександрЕрмолин That is quite true. In that sense, I just answered my own question in update lol. – Anthony Dec 29 '18 at 15:42

1 Answers1

2

If you are still looking for an answer about what does [$-130000] represent then check out my other answer here.

What does the 130000 in Excel locale code [$-130000] mean?

I am not certain how to do a reverse conversion. If you try to do the reverse of the above function, which would be...

=TEXT(A1, "[$-1010409]d/m/yyyy")

But this simply takes the presented date, and converts it to the stated format (i.e. it sees whatever in "A1" as a Gregorian date already)

IrwinAllen13
  • 547
  • 5
  • 11