Trump
Lv 7
Trump 發問時間: 電腦與網際網路軟體 · 1 0 年前

Excel-用「自訂函數」計算各種日期單位之間的差異值?

請教各位先進,

在 Excel 中的雖有提供 DATEDIF, EOMONTH 等函數,然 DATEDIF 函數卻存在著一小部份的 BUG。

詳見 http://tw.knowledge.yahoo.com/question/question.ph... 中筆者與 WhiteFox 之回覆與評論

另外亦可參考 Microsoft 官方網站 http://office.microsoft.com/zh-tw/help/HA011609811...

再參閱 GONG 之回覆 http://qa.pcuser.com.tw/modules/newbb/viewtopic.ph...

而計算兩日期之間的各種單位,卻是 Excel 使用者經常需要使用。

雖 Excel VBA 中已有提供 DATEDIFF 函數,但是大多數 Excel 使用者對 VBA 涉獵不深,因而無法運用之。

有鑑於此,希冀各位先進能以「自訂函數」,進而達到讓更多網友受惠。

此一自訂函數需求如下:

1.能以參數方式,決定兩日期之間差異值的日期單位,如:年、季、月、日

2.能以參數方式,決定兩日期之間差異值的日期單位:忽略日期中的年之季數、忽略日期中的年之月數、忽略日期中的的年與月之日數

自訂函數之各參數構想如下:

myDateDif(start_date,end_date,unit)

start_date 表「起始日」

end_date 表「終止日」

unit 表「日期單位」

希冀各方好手,能不吝出手,完成此一大作,Trump 萬分感謝您。

9 個解答

評分
  • 世賢
    Lv 7
    1 0 年前
    最佳解答

    自訂函數是要用在 Excel 儲存格內或者是 VBA 程式設計內呢?

    2007-10-24 18:12:07 補充:

    我的 Function 是已經寫好了,但是無法像 =Sum 函數一樣使用在儲存格內,請指教使用方式。

    2007-10-24 19:11:04 補充:

    Function myDateDif(ByVal start_date As Date, ByVal end_date As Date) As String '主函數

     y1 = Year(start_date)

     y2 = Year(end_date)

     m1 = Month(start_date)

     m2 = Month(end_date)

     d1 = Day(start_date)

     d2 = Day(end_date)

     If d1 = lastday(y1, m1) And d2 = lastday(y2, m2) Then '兩日期均為該年月之最後一天

      d1 = 0

      d2 = 0

      myDateDif = IIf(y2 - y1 - IIf(m2 >= m1, 0, 1) = 0, "", y2 - y1 - IIf(m2 >= m1, 0, 1) & "年")

      myDateDif = IIf(m2 - m1 + IIf(m2 < m1, 12, 0) = 0 And myDateDif = "", "", myDateDif & m2 - m1 + IIf(m2 < m1, 12, 0) & "月")

      myDateDif = myDateDif & d2 - d1 & "日"

     Else

      If d1 <> lastday(y1, m1) And d2 <> lastday(y2, m2) Then '兩日期均不是該年月最後一天

       myDateDif = IIf(y2 - y1 - IIf(m2 >= m1, 0, 1) = 0, "", y2 - y1 - IIf(m2 >= m1, 0, 1) & "年")

       myDateDif = IIf(m2 - m1 + IIf(m2 < m1, 12, 0) = 0 And myDateDif = "", "", myDateDif & m2 - m1 + IIf(m2 < m1, 12, 0) & "月")

       myDateDif = myDateDif & d2 - d1 & "日"

      Else

       If d1 = lastday(y1, m1) Then '起始日期為該年月最後一天

        myDateDif = IIf(y2 - y1 - IIf(m2 > m1, 0, 1) = 0, "", y2 - y1 - IIf(m2 > m1, 0, 1) & "年")

        myDateDif = IIf(m2 - m1 + IIf(m2 <= m1, 12, 0) - 1 = 0 And myDateDif = "", "", myDateDif & m2 - m1 + IIf(m2 <= m1, 12, 0) - 1 & "月")

        myDateDif = myDateDif & d2 & "日"

       Else '終止日期為該年月最後一天

        ' d2 = lastday(y2, m2)

        d1 = lastday(y1, m1) - d1

        myDateDif = IIf(y2 - y1 - IIf(m2 >= m1, 0, 1) = 0, "", y2 - y1 - IIf(m2 >= m1, 0, 1) & "年")

        myDateDif = IIf(m2 - m1 + IIf(m2 < m1, 12, 0) = 0 And myDateDif = "", "", myDateDif & m2 - m1 + IIf(m2 < m1, 12, 0) & "月")

        myDateDif = myDateDif & d1 & "日"

       End If

      End If

     End If

     

    End Function

    Function lastday(ByVal y As Integer, ByVal m As Integer) As Integer '副函數:求年月之最後一天

     lastday = 30 + ((m + (m > 7)) Mod 2) + (2 + (y Mod 4 = 0) + (y Mod 100 = 0) * (y Mod 400 <> 0)) * (m = 2)

    End Function

    2007-10-25 21:56:40 補充:

    to worlonzeng:

    在你發表意見前,我已經找到程式的 Bug 囉:

    2000/1/302001/4/11年2月2日

    2000/1/292001/4/21年2月4日

    2000/1/282001/4/31年2月6日

    2000/1/272001/4/41年2月8日

    2000/1/262001/4/51年2月10日

    2000/1/252001/4/61年2月12日

    2000/1/242001/4/71年2月14日

    2000/1/232001/4/81年2月16日

    2007-10-27 10:04:03 補充:

    修改之完整版

    2007-10-27 10:04:14 補充:

    http://ldluliu.myweb.hinet.net/excel/1607102302834...

    2007-10-28 18:15:49 補充:

    第一次修正:修正 2000/3/30 ~ 2001/3/1 會有負數日期的問題。

    http://ldluliu.myweb.hinet.net/excel/1607102302834...

    2007-10-28 18:16:56 補充:

    那是因為潤月的問題(當然是指二月份),均可做以下解釋:

     1. 2002/2/5 ~ 2003/2/4 = 11月30日 ok

      2002/2/5 ~ 2003/2/5 才算剛好滿一年

     2. 2000/2/28 ~ 2001/2/1 = 11月4日 起始日期非月底

      2000/2/29 ~ 2001/2/1 = 11月1日 起始日期為月底

      第二個問題其實每個月份(日期不變,月份變)都會出現這種情況。

    2007-10-30 22:05:07 補充:

    to worlonzeng:

     人家可不是為了工作天數而特地寫這個程式的喔!所以您的問題我在此便不回答囉!

    感謝 Trump 幫我寫註解!

    2007-10-30 22:24:31 補充:

    但好像寫錯了說。

    2007-10-30 22:25:15 補充:

    d1 = #3/31/1975#

    d2 = #2/28/2001#

    '不使用第四個參數,且第三個參數為 "":傳回相差完整年月日

    MsgBox myDateDif(d1, d2) '25年11月0日

    '不使用第四個參數,且第三個參數分別為 "y"、"m"、"d":分別傳回相差完整年、月、日

    MsgBox myDateDif(d1, d2, "y") '25

    MsgBox myDateDif(d1, d2, "m") '11

    MsgBox myDateDif(d1, d2, "d") '0

    2007-10-30 22:25:37 補充:

    '第三個參數為 "day":傳回相差總日數(年+月+日)

    MsgBox myDateDif(d1, d2, "day") '9466

    '第三個參數分別為 "w"、"ww"、"q":分別傳回工作日、星期、季

    MsgBox myDateDif(d1, d2, "w") '1352

    MsgBox myDateDif(d1, d2, "ww") '1352

    MsgBox myDateDif(d1, d2, "q") '104

    2007-10-30 22:25:48 補充:

    '若使用第四個參數,則會將每個月份均視為相同,即第四個參數的值

    '也就是:若第四個參數為 30,則每個月份均為 30 天。

    MsgBox myDateDif(d1, d2, , 30) '26年3月16日

    MsgBox myDateDif(d1, d2, "y", 30) '26

    MsgBox myDateDif(d1, d2, "m", 30) '3

    MsgBox myDateDif(d1, d2, "d", 30) '16

    2007-10-30 22:25:58 補充:

    '有無第四個參數,總整理如下:

    '若沒有使用第四個參數,則日期會按照大、小、閏月的規則做處理;相反的,若使用的話,則會按照第四個參數,將月份的總天數視為第四個參數,並不考慮大、小、閏月的處理。 End Sub

    '若第三個參數為 "day"、"w"、"ww"、"q",無論是否有使用第四個參數,傳回值均相同

  • 5 年前

    我本來從沒遇到過修NAS修硬碟和隨身碟要資料救援,一開始也跟大家一樣總是問價格到處比價,因為不懂,凡事都從價格,考量,輕言聽信朋友介紹比較便宜一家,找錯家之後痛苦尾隨而來,以下省略500字........,後來自己上網找資料救援找到硬碟醫院,和他們經理溝通之後給我正確觀念,這是妳寶貴的資料,妳要考慮是救不救的回問題,而不是貴不貴問題,如果重要請找對人搶救NAS資料硬碟救援才能恢復你的NAS中多顆的硬碟資料

    http://www.datamaster.com.tw/

  • 5 年前

    ●九州 娛樂 網站 http://ts777.cc

    ●●●運彩遊戲、真人遊戲、電子遊戲、對戰遊戲、對戰遊戲●●●

    ●新舊會員儲值就送500點

    ● 真人百家樂彩金等你拿

    ●線上影片直播、正妹圖、討論區免費註冊

    歡迎免費體驗交流試玩!

    ●九州 娛樂 網站 http://ts777.cc

  • 匿名使用者
    5 年前

    【亞洲36588合法彩券公司直營 官網: A36588.NET 】

    【 最新活動→迎接新會員,首存狂送20% 】

    【運動→電子→對戰→現場→彩球 】

    【免費服務 →電影區、討論區、KTV歡唱、運動轉播、即時比分、24H客服 】

    【亞洲36588合法彩券公司直營 官網: A36588.NET 】

  • 您覺得這個回答如何?您可以登入為回答投票。
  • 1 0 年前

    我的 myDateDif()也寫得差不多了, VBA 的 DateDiff 也有問題,

    DateDiff("YYYY", "1975/3/1", "2001/2/28") 得到 26,真討厭, 只好另外寫函數來解決, 但仍有其他問題待解決......

    To Liu-Liu:

    在VBE 裡插入模組, 把 Function 定義放在模組裡, 在工作表裡面就可以用它了

    2007-10-24 18:54:53 補充:

    worlonzeng的程式和我的有個相似的問題, 臥龍生大俠的是:

    2007/2/28 to 2008/2/28 ==> 1,0,0 (年月日)

    2007/2/28 to 2008/2/29 ==> 1,0,1

    2007/2/28 to 2008/3/1 ==> 1,0,1

    我的是這樣:

    2007/2/28 to 2008/2/28 ==> 1,0,0 (年月日)

    2007/2/28 to 2008/2/29 ==> 1,0,0

    2007/2/28 to 2008/3/1 ==> 1,0,1

    ^_*

    2007-10-28 19:42:59 補充:

    Function myDateDif(ByVal FDate As Date, ByVal TDate As Date, ByVal Interval As String) As Integer

    Select Case UCase(Interval)

    Case "Y":

     myDateDif = AgeY(FDate, TDate)

    Case "M", "Q", "D", "W", "WW", "YYYY":

     myDateDif = DateDiff(Interval, FDate, TDate)

    Case "MD":

     If TDate = EOMon(TDate) Then '若迄日是當月月底

      If FDate = EOMon(FDate) Then '若起日是當月月底

       myDateDif = 0 '則天數差為 (0)

      Else '若起日不是當月月底

       myDateDif = Day(EOMon(FDate)) - Day(FDate) '則天數差為 (起日當月月底 - 起日), 下個月為一整月

      End If

     Else '若迄日不是當月月底

      If Day(TDate) >= Day(FDate) Then '若迄日等於或大於起日

       myDateDif = Day(TDate) - Day(FDate) '則天數差為 (迄日 - 起日)

      Else '若迄日小於起日

       myDateDif = EOMon(FDate) - FDate + Day(TDate) '則天數差為 (迄日當月月底 - 起日 + 迄日)

      End If

     End If

    Case "YM":

     If Month(TDate) >= Month(FDate) Then '若迄月大於或等於起月

      myDateDif = Month(TDate) - Month(FDate) '則月數差為 (迄月 - 起月)

     Else '若迄月小於起月

      myDateDif = 12 - Month(FDate) + Month(TDate) '則月數差為 (12 - 起月 + 迄月)

     End If

     If (Day(TDate) < Day(FDate)) Then myDateDif = myDateDif - 1 '但若迄日小於起日, 則月數差要減1

    Case Else

     myDateDif = "Error"

    End Select

    End Function

    '傳回當月底的日期

    Function EOMon(ByVal DateX As Date) As Date

    Dim iYear, iMonth, iday As Integer

    Dim DateY As Date

    If IsDate(DateX) Then

     iYear = Year(DateX)

     iMonth = Month(DateX)

     If iMonth = 12 Then

      iMonth = 1

      iYear = iYear + 1

     Else

      iMonth = iMonth + 1

     End If

     EOMon = CDate(CStr(iYear) & "/" & CStr(iMonth) & "/1") - 1

    Else

     EOMon = 0

    End If

    End Function

    '計算年

    Function AgeY(ByVal FDate, TDate As Date) As Integer

    Dim tmpYear, tmpAge, tmpMonD, tmpMonth As Integer

    Dim FYear, FMonth, FDay As Integer

    FYear = Year(FDate)

    FMonth = Month(FDate)

    FDay = Day(FDate)

    tmpAge = 0

    tmpYear = FYear + 1

    While DateDiff("d", CDate(tmpYear & "/" & FMonth & "/" & FDay), TDate) >= 0

     tmpAge = tmpAge + 1

     tmpYear = tmpYear + 1

    Wend

    AgeY = tmpAge

    End Function

    2007-10-28 19:49:12 補充:

    我的程式邏輯還不夠嚴謹, (貼出來只為共襄盛舉, 不為點數)

    因故必須暫停發展此程式, 在此向大家道歉!

  • 1 0 年前

    拜讀各位大師、先進的意見與之前著作,小弟做了一個小小測試檔案,也許可提供各位先進、大師作為函數開發的參考~~^^

    小弟也用DATEDIF()加入運算,其中各種日期區間各個算法會衍生不同結果,至於對與錯,就見仁見智囉~~^^

    測試檔

    http://www.FunP.net/8215637

    2007-10-24 15:48:18 補充:

    小弟看法

    DATEDIF函數在月底有點BUG應該是確定的,不然天數不會有負數產生,但是人話的溝通方式卻是問題之關鍵所在

    如T兄上例

    如果前算後不算,或前不算後算,答案小弟認為應該是25年又11個月

    運用DATEDIF()參數中,前後參數都加1的目的在確認月底,而非加上經過天數

    所以想法上與W兄並不相同

    如果前後都算,答案小弟認為應該是25年又11個月又1天

    即如W兄的認知,但如用DATEDIF(),做法上卻是開始日+1,結束日+2

    2007-10-24 15:48:31 補充:

    不過這也是認知問題

    例如起始日1975/3/31 到期日2001/2/27 應該是幾年幾月幾天ㄋ

    1.25年10月27天(前算後不算,或前不算後算)

    2.25年10月28天(前後都算)

    3.25年11月(前後都算、因為從月底那一天,算到月底前一天)

    這種問題是見仁見智的吧~

    或是說要看計算結果使用的目的來運用與解釋吧~~^^

    2007-10-26 09:02:50 補充:

    各位先進好

    小弟有些疑惑了,以下日期應該算多久呢?

    2003/2/27到 2004/2/27 ,一年

    2003/2/27到 2004/2/28 ,一年又1天

    2003/2/27到 2004/2/29 ,一年又2天

    2003/2/28到 2004/2/27 ,11個月又27天

    2003/2/28到 2004/2/28 ,11個月又28天

    2003/2/28到 2004/2/29 ,1年

    這樣的邏輯對嗎?

    這是用DATEDIF(),修正後算的,有哪位大大可以解說一下這其中的問題?

    這是可以接受的嗎?

    2007-10-26 10:02:16 補充:

    另一個問題,哪位大哥可以看看小弟測試檔中,修正DATEDIF()函數中,帶入什麼樣的日期會發生錯誤?

    這樣才知道DATEDIF()運算是那裡有問題,才能做出更切合需求的函數.

  • 1 0 年前

    經 worlonzeng 大大指點,我原先的公式有點問題。

    思考邏輯應該是對的,錯是錯在我在一般情形下直接借用 datedif() 的結果,但是 datedif() 有問題的情況比我預期的還多。

    所以,要直接套用我的錯誤公式前,請再多想一下 :)

    2007-10-24 19:29:56 補充:

    寫了個程式,意見欄放不下,改放在我的格落格的回應中,麻煩大家看一下

    http://tw.myblog.yahoo.com/whitefox-blog/article?m...

    測試結果如下

    2007/1/30 ~ 2007/3/1 為 0年1月2日

    2007/2/28 ~ 2008/2/28 為 1年0月0日

    2007/2/28 ~ 2008/2/29 為 1年0月0日

    2007-10-24 19:32:58 補充:

    上面的程式目前支援 unit: "y" for year, "m" for month, "d" for day

    季的話,直接用 (year * 4) + (month / 3) 應該可行。

    週的話,要怎麼定義?day / 7 ?或是?

    2007-10-25 14:25:07 補充:

    和 worlonzeng 大大在討論一個問題

    2003/2/28 ~ 2004/2/28 算一整年?還是算11個又28天?

    2003/2/28 ~ 2004/2/29 算一整年?還是算一整年又一天?

    如果兩個都算一整年,會和奇怪嗎?

    大家覺得如何?

  • 1 0 年前

    原本想照 WhiteFox 大大的公式直接轉成 VBA,可是發現 Excel 增益集的函數在 VBA 中不能用,所以要花點時間自己寫了

    還是大家把原則說清楚,全部重寫算了

  • Trump
    Lv 7
    1 0 年前

    非常謝謝 w 兄回應。

    關於這部份,可參考 WhiteFox 部落格中的標準。

    http://tw.myblog.yahoo.com/whitefox-blog/article?m...

    2007-10-23 16:19:07 補充:

    不知 w 兄是否還有其他建議?

    如增加第四個引數來控制?

    2007-10-23 16:20:04 補充:

    Access 也有提供 DATEDIFF 函數,請見:

    http://office.microsoft.com/zh-tw/access/HA0122881...

    2007-10-23 21:53:52 補充:

    W 兄:

    基本上個人以為,只要能與 Excel VBA 中的 DateDiff 加上 Access 中的 DateDiff 函數吻合,在下以為如此就是客觀的了。

    再度感謝您,勞心了~

    2007-10-24 14:34:53 補充:

    W 兄,

    請見上面在下之前的回覆,有疑問,請繼續討論之。

    2007-10-24 14:59:01 補充:

    1. 假設 B1 為 1975/3/31 , B2 為 2001/02/28

    2. 依照您所提供的通用法則 =DATEDIF(B1,B2+1,"MD")

    傳回值為 -2

    這是否意味著 DATEDIF 此函數之無法通用之故?

    2007-10-24 15:15:30 補充:

    W 兄,

    若有需要,在下可以替你代言。

    透過 mail or msn 皆可。

    2007-10-24 17:35:22 補充:

    Excel 中的 VBA 可以宣告自訂函數,提供給工作表中使用。

    語法:

    [Public | Private | Friend] [Static] Function name [(arglist)] [As type]

    [statements]

    [name = expression]

    [Exit Function]

    [statements]

    [name = expression]

    End Function

    2007-10-24 18:18:39 補充:

    Liu 兄:

    請參閱上述在下的補充意見。

    或是建議您貼出,由其他高手代為局部修改呢?

    2007-10-24 18:34:23 補充:

    再度感謝各位,熱情支持,Trump 由衷祝福各位!

    2007-10-24 19:01:46 補充:

    在下手上有 Excel VBA 之農民曆自訂函數,不知是否對各位有所幫助?

    需要者,請透過以下網址來信索取,謝謝。

    http://tw.knowledge.yahoo.com/my/mailto_profile?ki...

    2007-10-24 19:18:04 補充:

    各位,辛苦了!

    在下有個不情之請,是否可將季、週併入此一自訂函數中呢?

    乃至使用第三個參數,來決定哪種使用日期單位呢?

    再度感謝~

    2007-10-24 20:04:29 補充:

    WhiftFox 非常感協你熱情支持!

    關於「週」之定義,在下以為比較客觀的著眼點,可參閱 Access 之 DateDiff 說明。

    http://office.microsoft.com/zh-tw/access/HA0122881...

    2007-10-29 21:48:50 補充:

    非常感謝所有參與的各位前輩、好友,現在大致上已有幾位高手已經完成,請各位撥冗進行測試與後續相關討論。

    另外,也請已經發表之高手,可否能針對該「自訂函數」之用法,簡要說明之呢?

    再度感謝,在下致上十二萬分謝意。

    2007-10-29 21:55:55 補充:

    Liu 前輩所最後發表之「自訂函數」,參數共有兩個部份。

    ■ 第三個參數,用法如下:

    "":傳回相差完整年月日(以 md 為單位)

    "y":傳回相差年(以 md 為單位)

    "m":傳回相差月(以 md 為單位)

    "d":傳回相差日(以 md 為單位)

    "w":工作日

    "ww":星期

    2007-10-29 21:56:19 補充:

    Liu 前輩所最後發表之「自訂函數」

    ■ 第四個參數,用法如下:

    若沒有使用第四個參數,則日期會按照大、小、閏月的規則做處理;相反的,若使用的話,則會按照第四個參數,將月份的總天數視為第四個參數,並不考慮大、小、閏月的處理。

    2007-11-07 08:21:36 補充:

    由於「評價」字數限制,筆者感謝所有參與的高手、網友!

    待 Liu-Liu 稍加修正「季」之問題之後,筆者將發表針對此自訂函數完整的使用方法。

還有問題?馬上發問,尋求解答。