以工作表行事曆找當月最後一天的前一天

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2025-1-2

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

2^#

發表於 2013-10-7 09:02 | 只看該作者

本帖最後由 p212 於 2013-10-7 09:04 編輯

回復 1# myleoyes
原問描述：「A1所需求的日期為當月最後一天的前一天，也就是說範例在8月為8/29日是正確，9月為9/27日是正確...」

8/29與9/27皆不為當月最後一天的前一天 (8月最後一天8/31、9月最後一天9/30)
請問您要的是哪一天？

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2013-10-7 12:50 | 只看該作者

C1 =today() 或輸入日期
A1 =LARGE((TEXT(C1,"emm")=TEXT(行事曆!A$3:A$9999,"emm"))*行事曆!A$3:A$9999,2)
公式需用陣列輸入(CTRL+SHIFT+ENTER三鍵齊按輸入)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

4^#

發表於 2013-10-7 22:05 | 只看該作者

回復 2# p212
前輩!
      謝謝!!不好意思表達不清楚
      一般的行事曆8月最後一天8/31、9月最後一天9/30
      以工作天的行事曆8月最後一天8/30、9月最後一天9/30
      然而現實的生活裡這兩種行事曆都無法滿足實際所需
      所以範例的行事曆是依實際與合乎老闆要求而製作
      因此行事曆工作表A欄日期是由程式寫入
      每一個工作天寫入一儲存格,所以設定A欄日期的最大日期
      永遠大於今日工作天3天(應該說多3個儲存格)
      因此範例的9/27是9月最後一天的前一天不是嗎?
      前輩請以行事曆工作表的列數來辨別日期
      這樣就比較容易清楚
      也就是說範例的9/30是A44那A43不就是9/27日
      不知道前輩是否明白小弟的意思
      請不吝再賜教謝謝再三!!

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

5^#

發表於 2013-10-7 22:08 | 只看該作者

回復 3# ML089
前輩!
謝謝!!公式有誤是因為小弟思表達不清楚
假設今天是8月29日所以開檔程式在
行事曆工作表A欄下拉一儲存格所以A26是9/3日
將A1公式改為A1=DATE(YEAR(MAX(行事曆!A3:A25000)),MONTH(MAX(行事曆!A3:A25000)),-2)
就是小弟所要的日期請將範例行事曆工作表A27之後的儲存格全清除
A1公式如上8月最後一天的前一天不就是8/29日嗎?A1顯示如此
然而日子一天天的過去儲存格一格格的往下拉至範例所示A50
再將A1公式 -2 改為 -3 那9/27不就是9月最後一天的前一天
再下拉行事曆工作表A欄儲存格至A68
再將A1公式 -3 改為 -1 那10/30不就是10月最後一天的前一天
所以A1=DATE(YEAR(MAX(行事曆!A3:A25000)),MONTH(MAX(行事曆!A3:A25000)),0)
是當月最後一天因此將公式改來改去以求符合所需這是不可能是
所以因應有適用的公式可以解決這個問題
前輩請以行事曆工作表的列數來辨別日期
這樣就比較容易清楚
不知道前輩是否明白小弟的意思
請不吝再賜教謝謝再三!!

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2013-10-8 09:19 | 只看該作者

A1=LOOKUP(LOOKUP(DATE(YEAR(MAX(行事曆!A3:A25000)),MONTH(MAX(行事曆!A3:A25000)),),行事曆!A3:A25000)-1,行事曆!A3:A25000)
一般公式

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2013-10-8 09:23 | 只看該作者

如果EXCEL2007版以上，可用下式

A1=LOOKUP(LOOKUP(DATE(YEAR(MAX(行事曆!A:A)),MONTH(MAX(行事曆!A:A)),),行事曆!A:A)-1,行事曆!A:A)

或將行事曆!A:A 用名稱替代較容易維護公式

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

8^#

發表於 2013-10-8 21:45 | 只看該作者

回復 7# ML089
前輩!
   謝謝!!公式以最後一天的前一天是OK!
   但有一問題令小弟不解將公式改為 -2
   測試整年度只有9月有誤減2還是9/27日
   應該是9/26日才對
   改為 -3
   測試整年度減3還是9/27日
   應該是9/25日才對
   且4月與12月也都少減一列日期
   應該是4/25日才對
   應該是12/26日才對
   為何如此難道此公式僅適用減1嗎?
   有何公式可以彈性些減2減3減4
   皆能準確顯示這樣公式將來會很常用的
   請不吝再賜教辛苦囉!謝謝再三!!

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

9^#

發表於 2013-10-8 23:46 | 只看該作者

=LARGE(IF(DATE(YEAR(MAX(行事曆!A3:A999)),MONTH(MAX(行事曆!A3:A999)),)>=行事曆!A3:A999,行事曆!A3:A999),2)
陣列公式(CTRL+SHIFT+ENTER三鍵齊按輸入公式)

公式最後面 2 是指前一月最後第2日

明天出國下星期回來，有問題請等幾天或請其他人幫忙

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

10^#

發表於 2013-10-10 10:45 | 只看該作者

回復 9# ML089
前輩!
   公式OK!謝謝你在繁忙之中解答!
   不勝感激小弟順祝你旅程愉快!
   謝謝再三!!