如何往前推算日期的問題。

WOW9637

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

下載 (128.2 KB)

2015-11-26 09:58

有辦法以公式來完成嗎?，麻煩各位了。

WOW9637

更正一下，下圖才是對的，謝謝

下載 (130.11 KB)

2015-11-26 10:45

hcm19522

http://blog.xuite.net/hcm19522/twblog/360124342

WOW9637

回復 3# hcm19522

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

yen956

因 "前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))

下載 (462.68 KB)

2015-11-27 04:52

WOW9637

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

yen956大大，謝謝您的幫忙，是否能麻煩您將檔案放置雲端中，小弟目前沒辦法使用下載附件功能，謝謝。

yen956

回復 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

http://blog.xuite.net/hcm19522/twblog/360456424

yen956

想到更簡捷的方法:
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
    
17. 
    
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
    
28. 
    
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
    
39. 
    
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

複製代碼

下載 (433.81 KB)

2015-11-28 20:12

WOW9637

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