Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - What does bb or bbbb represent in an EN-US TEXT function or Custom Number format mask?

On a EN-US regional system, what does B or b represent in a format mask? While it does nothing in VBA's Format function, both the TEXT function and Custom Number Formatting recognize b as a legal Number Format Code and return a number that I cannot determine the origin of.

To further confuse matters, larger numbers return different values when formatted with bbbb and while blocks of sequential numbers return the same value, there are transitions when they jump ahead a digit. I do know that Excel expects the format mask to be either bb or bbbb because if B or b is supplied as a custom number format Type: then it is corrected to bb and the same goes for BBB or bbb being corrected to bbbb.

??BBBB format mask

If you look at the digit transitions in the above image then this must have something to do with calendar years and leap years. The transitions appear to generally be 365 between them except for every fourth which is 366. Is the 24 the number of leap years since 01-Jan-1900 according to the current system clock?

Might this be a format mask character for a non-Gregorian calendar system? I can find no documentation (official or otherwise) on the subject.


I've been told that this behavior is not exhibited on all systems. For me, this occurs in both XL2010 Pro Plus and XL2013 Pro Plus (both EN-US 64 bit Office on 64 bit EN-CA OS)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

bb and bbbb appear to be formatting codes for the Buddhist calendar year, which are in use in Thailand and are simply the Gregorian year plus 543. bbbb is 4 digits and bb is two digits, as for yyyy and yy.

Excel has a date representation which consists of the count of days beginning with Jan 1 1900 (except when it's Jan 1 1904). So 1 == Jan 1 1900, which is Jan 1 2443 Buddhist, and you're formatting the year, 2443.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...