Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does the $ sign mean in TEXT function

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.

like image 793
Anthony Avatar asked Dec 27 '18 16:12

Anthony


People also ask

Which character is used in text formula?

Solution. The Ampersand character is used in text formula.


1 Answers

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)

like image 137
IrwinAllen13 Avatar answered Sep 21 '22 02:09

IrwinAllen13