Board logo

標題: 如何往前推算日期的問題。 [打印本頁]
作者: WOW9637    時間: 2015-11-26 09:59     標題: 如何往前推算日期的問題。

請問各位先進，目前小弟有一需求，如A2欄位11/5，對應到B2欄位，往前推算3天，往前推算3天的過程中，如過碰到六、日、假日則跳過。
[attach]22613[/attach]

有辦法以公式來完成嗎?，麻煩各位了。
作者: WOW9637    時間: 2015-11-26 10:45

更正一下，下圖才是對的，謝謝
[attach]22617[/attach]
作者: hcm19522    時間: 2015-11-26 11:26

http://blog.xuite.net/hcm19522/twblog/360124342
作者: WOW9637    時間: 2015-11-26 14:05

回復 3# hcm19522

hcm19522大大，非常謝謝您，目前我前三天、前六天的都沒問題了。
但是關於前14天的比較特別，必須要有3天含以上的假日，如六、日、一連假，才會扣除
反之如果沒有三天以上，則六日及假日則不扣除。
作者: yen956    時間: 2015-11-27 04:53

因 "前14天的比較特別，必須要有3天含以上的假日，如六、日、一連假，
才會扣除,   如果沒有三天以上，則六日及假日則不扣除。"
的關係, 我想到用 "日柱" 來處理,  "日柱" 取自
http://forum.twbts.com/thread-11971-1-1.html
使用前請先核對, 因該 "連假" 係根據公家機關而訂, 私人機構可能要修正。

一、建立 輔助欄 "xx" 及 "yy"
1. 插入欄B及欄C
2. 將欄A複製到欄B及欄C
3. 將欄B及欄C 標題改為 xx 及 yy
4. 刪除欄B所有假日儲存格(下方儲存格上移)
(可手動或執行下列VBA Code)
Sub 刪除假日()
    Dim I As Integer
    Sheets("Sheet2").Activate
    For I = 460 To 2 Step -1
        If Cells(I, 2).Font.ColorIndex = 3 Or Cells(I, 2).Font.ColorIndex = 5 Then
            Cells(I, 2).Delete Shift:=xlUp
        End If
    Next
End Sub
5. (手動)將 欄C 所有連續3天以上的
     儲存格全部刪除(下方儲存格上移)

二、建立名稱 "上線日","xx","yy"
1. 選取 "A1:C428"
2. 按 Ctrl + Shift + F3
3. 勾選[頂端列](其餘不可選)

三、將 欄B及欄C 剪下 貼上到 欄x及欄y
四、輸入公式
[B17]公式, 向下拉
=IF(ISERROR(MATCH(上線日,xx,0)),"",INDIRECT("X"&MATCH(上線日,xx,0)-2))
[C17]公式, 向下拉
=IF(ISERROR(MATCH(上線日,xx,0)),"",INDIRECT("X"&MATCH(上線日,xx,0)-5))
[D17]公式, 向下拉
=IF(ISERROR(MATCH(上線日,yy,0)),"",INDIRECT("Y"&MATCH(上線日,yy,0)-13))
[attach]22624[/attach]
作者: WOW9637    時間: 2015-11-27 08:25

因 "前14天的比較特別，必須要有3天含以上的假日，如六、日、一連假，
才會扣除,   如果沒有三天以上，則六 ...
yen956 發表於 2015-11-27 04:53


yen956大大，謝謝您的幫忙，是否能麻煩您將檔案放置雲端中，小弟目前沒辦法使用下載附件功能，謝謝。
作者: yen956    時間: 2015-11-27 09:14

回復 6# WOW9637

http://www.mediafire.com/download/qxvugva5w6qa9a9/%E5%90%91%E5%89%8D%E6%8E%A8%E4%B8%89%E5%A4%A9.rar
作者: hcm19522    時間: 2015-11-27 14:41

http://blog.xuite.net/hcm19522/twblog/360456424
作者: yen956    時間: 2015-11-28 20:12

想到更簡捷的方法:
1. 建立 連假表
將欄A中所有連假三天以上的儲存格,
全部複製到欄G
2. 在Module1輸入下列VBACode
  1. Function BackWardDays(ByVal Num As Integer, date2 As Date) As Date
  2.     Dim cnt As Integer, MH, sh As Object
  3.     Set sh = Sheets("Sheet1")
  4.     cnt = 0
  5.     Do
  6.         date2 = date2 - 1
  7.         MH = Application.Match(CLng(date2), sh.[G2:G31], 0)
  8.         cnt = cnt + 1
  9.         If Num < 14 Then
  10.             If Weekday(date2, vbMonday) > 5 Or IsNumeric(MH) Then cnt = cnt - 1
  11.         Else
  12.             If IsNumeric(MH) Then cnt = cnt - 1
  13.         End If
  14.     Loop Until cnt = Num
  15.     BackWardDays = date2
  16. End Function

  18. Sub 三天前()
  19.     Dim date1 As Date, sh As Object
  20.     Dim I As Integer
  21.     Set sh = Sheets("Sheet1")
  22.     sh.[B2:B400] = ""
  23.     For I = 2 To sh.[A2].End(xlDown).Row
  24.        date1 = BackWardDays(3, sh.Cells(I, 1))
  25.        sh.Cells(I, 2) = date1
  26.     Next
  27. End Sub

  29. Sub 六天前()
  30.     Dim date1 As Date, sh As Object
  31.     Dim I As Integer
  32.     Set sh = Sheets("Sheet1")
  33.     sh.[C2:C400] = ""
  34.     For I = 2 To sh.[A2].End(xlDown).Row
  35.        date1 = BackWardDays(6, sh.Cells(I, 1))
  36.        sh.Cells(I, 3) = date1
  37.     Next
  38. End Sub

  40. Sub 十四天前()
  41.     Dim date1 As Date, sh As Object
  42.     Dim I As Integer
  43.     Set sh = Sheets("Sheet1")
  44.     sh.[D2:D400] = ""
  45.     For I = 2 To sh.[A2].End(xlDown).Row
  46.        date1 = BackWardDays(14, sh.Cells(I, 1))
  47.        sh.Cells(I, 4) = date1
  48.     Next
  49. End Sub
複製代碼
[attach]22645[/attach]
作者: WOW9637    時間: 2015-11-28 20:30

謝謝各位的幫忙 感激不盡!!。



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)