返回列表 上一主題 發帖

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

WOW9637

帖子
16 
主題
4 
精華
0 
積分
24 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE2007 
閱讀權限
10 
註冊時間
2014-6-26 
最後登錄
2019-1-31 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2015-11-26 09:59 | 只看該作者

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

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


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

WOW9637

帖子
16 
主題
4 
精華
0 
積分
24 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE2007 
閱讀權限
10 
註冊時間
2014-6-26 
最後登錄
2019-1-31 
2#
發表於 2015-11-26 10:45 | 只看該作者
更正一下，下圖才是對的，謝謝

TOP

hcm19522

帖子
1387 
主題
4 
精華
0 
積分
1397 
點名
0  
作業系統
WIN 
軟體版本
2007 
閱讀權限
50 
性別
男 
註冊時間
2015-9-11 
最後登錄
2024-11-27 

熱烈參與勳章
3#
發表於 2015-11-26 11:26 | 只看該作者
http://blog.xuite.net/hcm19522/twblog/360124342

TOP

WOW9637

帖子
16 
主題
4 
精華
0 
積分
24 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE2007 
閱讀權限
10 
註冊時間
2014-6-26 
最後登錄
2019-1-31 
4#
發表於 2015-11-26 14:05 | 只看該作者
回復 3# hcm19522

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

TOP

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
5#
發表於 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))
test.gif

向前推三天.rar (114.21 KB)

TOP

WOW9637

帖子
16 
主題
4 
精華
0 
積分
24 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE2007 
閱讀權限
10 
註冊時間
2014-6-26 
最後登錄
2019-1-31 
6#
發表於 2015-11-27 08:25 | 只看該作者
因 "前14天的比較特別，必須要有3天含以上的假日，如六、日、一連假，
才會扣除,   如果沒有三天以上，則六 ...
yen956 發表於 2015-11-27 04:53


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

TOP

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
7#
發表於 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

TOP

hcm19522

帖子
1387 
主題
4 
精華
0 
積分
1397 
點名
0  
作業系統
WIN 
軟體版本
2007 
閱讀權限
50 
性別
男 
註冊時間
2015-9-11 
最後登錄
2024-11-27 

熱烈參與勳章
8#
發表於 2015-11-27 14:41 | 只看該作者
http://blog.xuite.net/hcm19522/twblog/360456424

TOP

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
9#
發表於 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
複製代碼
test.gif

TOP

WOW9637

帖子
16 
主題
4 
精華
0 
積分
24 
點名
0  
作業系統
WIN7 
軟體版本
OFFICE2007 
閱讀權限
10 
註冊時間
2014-6-26 
最後登錄
2019-1-31 
10#
發表於 2015-11-28 20:30 | 只看該作者
謝謝各位的幫忙 感激不盡!!。

TOP

        靜思自在 : 對父母要知恩，感恩、報恩。
返回列表 上一主題