Board logo

標題: 以工作表行事曆找當月最後一天的前一天 [打印本頁]
作者: myleoyes    時間: 2013-10-6 16:02     標題: 以工作表行事曆找當月最後一天的前一天

各位前輩你們好!!   
         A1=DATE(YEAR(MAX(行事曆!A3:A25000)),MONTH(MAX(行事曆!A3:A25000)),-3)
         問題如附檔說明
         請知道的前輩,不吝賜教謝謝再三!!
作者: p212    時間: 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)
請問您要的是哪一天？
作者: ML089    時間: 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三鍵齊按輸入)
作者: myleoyes    時間: 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日
          不知道前輩是否明白小弟的意思
          請不吝再賜教謝謝再三!!
作者: myleoyes    時間: 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)
    是當月最後一天因此將公式改來改去以求符合所需這是不可能是
    所以因應有適用的公式可以解決這個問題
    前輩請以行事曆工作表的列數來辨別日期
    這樣就比較容易清楚
    不知道前輩是否明白小弟的意思
    請不吝再賜教謝謝再三!!
作者: ML089    時間: 2013-10-8 09:19

A1=LOOKUP(LOOKUP(DATE(YEAR(MAX(行事曆!A3:A25000)),MONTH(MAX(行事曆!A3:A25000)),),行事曆!A3:A25000)-1,行事曆!A3:A25000)
一般公式
作者: ML089    時間: 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 用名稱替代較容易維護公式
作者: myleoyes    時間: 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
     皆能準確顯示這樣公式將來會很常用的
     請不吝再賜教辛苦囉!謝謝再三!!
作者: ML089    時間: 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日

明天出國下星期回來，有問題請等幾天或請其他人幫忙
作者: myleoyes    時間: 2013-10-10 10:45

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



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