Excel的日期格式約定與解析

原理

當(dāng)Excel單元格內(nèi)容為=NOW()時(shí)，會(huì)顯示當(dāng)前的日期/時(shí)間，而倘若你將設(shè)置為單元格格式改為非日期時(shí)間的格式時(shí)，會(huì)顯示一個(gè)非負(fù)實(shí)數(shù)。

Excel 支持兩個(gè)日期系統(tǒng)：1900年日期系統(tǒng)（推薦）和 1904年日期系統(tǒng)。每個(gè)日期系統(tǒng)使用日期作為計(jì)算的所有其他工作簿中的唯一開始日期。所有版本的 Excel for Windows 都計(jì)算基于 1900年日期系統(tǒng)中的日期。Excel 2008 for Mac 和早期 Excel for Mac 版本計(jì)算基于 1904年日期系統(tǒng)的日期。Excel 2016 for Mac 和 Excel for Mac 2011 使用 1900年日期系統(tǒng)，保證日期與 Excel for Windows 的兼容性。

就目前而言，正常情況下這個(gè)非負(fù)實(shí)數(shù)的整數(shù)位表示該日期距離1900年1月1日的天數(shù)，小數(shù)位表示該日期的當(dāng)天時(shí)間占據(jù)整天時(shí)間的比例，因此可以很方便的用代碼將其解析成我們的日常使用日期系統(tǒng)。

JAVA實(shí)現(xiàn)

// excelDateAnalysis.javapackage ms.excelDateAnalysis;import java.util.Calendar; import java.util.GregorianCalendar;public class excelDateAnalysis {private static final int SECONDS_PER_MINUTE = 60;private static final int MINUTES_PER_HOUR = 60;private static final int HOURS_PER_DAY = 24;private static final int SECONDS_PER_DAY = (HOURS_PER_DAY *MINUTES_PER_HOUR *SECONDS_PER_MINUTE);private boolean onlyTime = false;private double excelDate;private int sumDay, sumSecond;private int year, month, day, week;private int hour, minute, second;public boolean onlyTime() { return this.onlyTime; }public int getSumDay () { return this.sumDay; }public int getSumSecond() { return this.sumSecond; }public int getYear () { return this.year; }public int getMonth () { return this.month; }public int getDay () { return this.day; }public int getWeek () { return this.week; }public int getHour () { return this.hour; }public int getMinute () { return this.minute; }public int getSecond () { return this.second; }public void test() {excelDateAnalysis eDate = new excelDateAnalysis(this.excelDate);System.out.println("sum second in one day: " + eDate.getSumSecond());System.out.println("sum days: "+eDate.getSumDay());if (!eDate.onlyTime) {System.out.println("Date: " + eDate.getYear() +"/" + eDate.getMonth() +"/" + eDate.getDay());}System.out.println("Time: " + eDate.getHour() +":" + eDate.getMinute() +":" + eDate.getSecond()); }public excelDateAnalysis(double excelDate) {this.excelDate = excelDate;this.sumDay = (int)Math.floor(excelDate);this.sumSecond = (int)Math.floor((excelDate-(double)this.sumDay)*SECONDS_PER_DAY+0.5);sumSecondAnalysis(this.sumSecond);sumDayAnalysis(this.sumDay);}private void sumDayAnalysis(int wholeDays) {if (wholeDays==0) {this.onlyTime = true;return;}Calendar calendar = new GregorianCalendar();boolean use1904windowing = false;int startYear = 1900;int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn'tif (use1904windowing) {startYear = 1904;dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day} else if (wholeDays < 61) {// Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists // If Excel date == 2/29/1900, will become 3/1/1900 in Java representationdayAdjust = 0;}calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);calendar.set(GregorianCalendar.MILLISECOND, this.sumSecond*1000);this.year = calendar.get(Calendar.YEAR);this.month = calendar.get(Calendar.MONTH)+1;this.day = calendar.get(Calendar.DAY_OF_MONTH);this.week = calendar.get(Calendar.DAY_OF_WEEK)-1;}private void sumSecondAnalysis(int sumSecond) {this.hour = sumSecond / SECONDS_PER_MINUTE / MINUTES_PER_HOUR;this.minute = sumSecond / SECONDS_PER_MINUTE - this.hour * MINUTES_PER_HOUR;this.second = sumSecond % SECONDS_PER_MINUTE;} }

使用起來也很簡(jiǎn)單：

// Main.javaimport ms.excelDateAnalysis.*;public class Main {public static void main(String[] args) {excelDateAnalysis eDate = new excelDateAnalysis(43529.50258);eDate.test(); // 測(cè)試輸出} }

二級(jí)辦公AOA中的應(yīng)用

E練習(xí)3-03(員工信息表）.xlsx 操作要求（2）在Sheet4中，使用函數(shù)，將B1中的時(shí)間四舍五入到最接近的15分鐘的倍數(shù)，結(jié)果存放在C1單元格中。
Sheet4.B1 <- {自定義}[13:49:38]

利用Excel內(nèi)建日期函數(shù)和四舍五入函數(shù)是能解決這道題目的（=TIME(HOUR(B1),15*ROUND(MINUTE(B1)/15,0),SECOND(0))或者=TIME(HOUR(B1),MROUND(MINUTE(B1),"15"),SECOND(0))），但問題是太過繁瑣，不夠直接，我們用本文提到的原理來解決。

60*24 = 1440

步驟公式解釋

1	B1*1440	將總分鐘數(shù)假定為總天數(shù)
2	Round(B1*1440/15,0)*15	讓總天數(shù)變成15分鐘的倍數(shù)
3	Round(B1*1440/15,0)*15/1440	將變成更改后的總天數(shù)變回默認(rèn)時(shí)間約定

Sheet4.C1 <- {自定義}[=Round(B1*1440/15,0)*15/1440]

13:49:38 === 0.576134259

娛樂時(shí)間

excelDateAnalysis eDatePI = new excelDateAnalysis(Math.PI);eDatePI.test();excelDateAnalysis eDateE = new excelDateAnalysis(Math.E);eDateE.test(); sum second in one day: 12234 sum days: 3 Date: 1900/1/3 Time: 3:23:54 sum second in one day: 62060 sum days: 2 Date: 1900/1/2 Time: 17:14:20

轉(zhuǎn)載于:https://www.cnblogs.com/unixart/p/10475960.html

總結(jié)

以上是生活随笔為你收集整理的Excel的日期格式约定与解析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。