麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 求問!!表格中月份的比對問題!! [打印本頁]

作者: rszo 時間: 2013-8-26 11:04 標題: 求問!!表格中月份的比對問題!!

現在表格中1998/6/1的年月日會以今天的時間2013/8/26下去跑總共有多長來輸出，是不是可以設定成2013/5/5 下去跑共經過多長時間來輸出?
附件表格中的C欄公式為:
=IF(B2<>"",DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"個月","")
會導致C欄輸出為 15年2個月

D欄公式:
=IF(C2<>"",DATEDIF(B2,TODAY(),"y"),"")
只看年分，月份無條件捨去

是不是能修改成:
A B C D
2013/5/5 1998/6/1 14年11個月 14年

請問要如何修改?

[attach]15854[/attach]

作者: p212 時間: 2013-8-26 12:24

本帖最後由 p212 於 2013-8-26 12:29 編輯

回復 1# rszo
以您提供的範例執行修改：
1、A2儲存格輸入2013/5/5
2、C2儲存格公式原有的TODAY()改成A2即可，C2儲存格修改後如下列公式：
=IF(B2<>"",DATEDIF(B2,A2,"y")&"年"&DATEDIF(B2,A2,"ym")&"個月","")
3、D2儲存格公式修改方式亦同
=IF(C2<>"",DATEDIF(B2,A2,"y"),"")
請問這樣可以嗎？

作者: rszo 時間: 2013-8-26 13:08

回復 2# p212

感謝回復!這樣改確實可以使用了!之前修改的時候不知道要把"()"一起修改掉，難怪都不會成功!

作者: rszo 時間: 2013-8-26 13:29

本帖最後由 rszo 於 2013-8-26 13:32 編輯

因為新手還在邊爬文邊實習的階段，對於IF的用法還不是很熟悉，另請教一個問題，若C欄剛好為14年0個月
要多加甚麼限制才能讓D欄輸出為13年?但是只有剛好月數為0的樂的情況才-1年，其他情況(EX:14年1個月以上)則維持14年

作者: rszo 時間: 2013-8-26 13:36

回復 4# rszo

超過3分鐘不能再編輯，這裡再補充一下
以上情況如果再加一個條件:1年0個月維持輸出1年，2年0個月以上才繼續套用上述情況要怎麼修改?

作者: p212 時間: 2013-8-26 13:39

本帖最後由 p212 於 2013-8-26 13:43 編輯

回復 4# rszo
D2儲存格公式修改為
=IF(C2<>"",YEARFRAC(B2,A2),"")
這樣可以嗎？
ps.小數位數請自行設定

作者: rszo 時間: 2013-8-26 14:11

十分感謝您的回復!
剛剛測試了一下，若2013/5/5可以讓有小數點的情況發生
但是若日期A2改成2013/7/31，D5則會輸出14.05
這樣修改好像只有某些特定日期能小於14(小數位數自行設定無條件捨去的話)
補充:以上情況如果再加一個條件:1年0個月維持輸出1年，2年0個月以上才繼續套用上述情況
這樣子有沒有其他方法修改呢?

作者: rszo 時間: 2013-8-26 15:04

回復 6# p212

不好意思，剛剛沒注意沒點到回復按鈕.

十分感謝您的回復!
剛剛測試了一下，若2013/5/5可以讓有小數點的情況發生
但是若日期A2改成2013/7/31，D5則會輸出14.05
這樣修改好像只有某些特定日期能小於14(小數位數自行設定無條件捨去的話)
補充:以上情況如果再加一個條件:1年0個月維持輸出1年，2年0個月以上才繼續套用上述情況
這樣子有沒有其他方法修改呢?

作者: rszo 時間: 2013-8-26 15:19

回復 6# p212

這是我剛剛測試出來的檔案
因為以後都想要以某月的月初或月尾來做標準時間下去run
遇到如D5或I2的情況則此方法不適用，能有其他方法嗎?

[attach]15855[/attach]

作者: p212 時間: 2013-8-26 16:12

本帖最後由 p212 於 2013-8-26 16:20 編輯

回復 9# rszo
請問
在start_date為1999/7/13且end_date為2013/7/31時，使用YEARFRAC函數精準地計算該二日期相差為「14.05」年之情況下，
您要的答案是「13」而不為「14」？抑另為其他答案？
可否再將條件整理一下(如範例檔加註欄位名稱、敘述用意等)以供大夥兒一齊討論？
謝謝！

作者: p212 時間: 2013-8-26 16:33

本帖最後由 p212 於 2013-8-26 16:39 編輯

回復 9# rszo
承10#回覆_您要的答案是「13」而不為「14」？
請參考下列作法
1、請在9#範例檔之D2儲存格輸入
=IF(C2="","",IF(DATEDIF(B2,$A$2,"ym")=0,INT(YEARFRAC(B2,$A$2)-1),INT(YEARFRAC(B2,$A$2))))
向下複製公式
2、請在9#範例檔之J2儲存格輸入
=IF(I2="","",IF(DATEDIF(H2,$G$2,"ym")=0,INT(YEARFRAC(H2,$G$2)-1),INT(YEARFRAC(H2,$G$2))))
向下複製公式

作者: rszo 時間: 2013-8-26 16:53

回復 10# p212

謝謝您花時間，在此我再整理一下，免得大家看得頭痛
在start_date為1999/7/13且end_date為2013/7/31時，使用YEARFRAC函數精準地計算該二日期相差為「14.05」年之情況下，
您要的答案是「13」而不為「14」？A:我希望得到的答案為13

我希望在C列中若是"月份為0"的話則D列輸出年份-1,但C列年份若為1的情況下D列輸出年份不-1,一切情況皆須C列中年份>1 and 月份為0 的情況下才執行.

我目前的邏輯是這樣(不知道有沒有邏輯錯誤還是太複雜化):
IF N年0月時 N=0則N=0 否則 IF N年0月時N=1.則N=1否則IF N年0月時 N-1否則N=N
不是很熟if的用法，嘗試好多次都沒辦法打出完整的公式請各位高手幫忙解惑

詳情附件:

[attach]15856[/attach]

作者: rszo 時間: 2013-8-26 17:13

本帖最後由 rszo 於 2013-8-26 17:15 編輯

回復 11# p212

再次的感謝您!以此公式下去跑可以執行
不過12#中新增的附件內1年0個月的情況下不想要年份-1的話這要怎樣增加if還是其他函數下去RUN呢?

作者: p212 時間: 2013-8-27 08:29

本帖最後由 p212 於 2013-8-27 08:32 編輯

回復 13# rszo
以12#提供的附件檔修改公式
請在D3儲存格輸入
=IF(C3="","",IF(AND(DATEDIF(B3,A3,"y")>1,DATEDIF(B3,A3,"ym")=0),INT(YEARFRAC(B3,A3)-1),INT(YEARFRAC(B3,A3))))

作者: rszo 時間: 2013-8-27 09:06

回復 14# p212

感謝您的回復!以您給的公式下去可以順利執行
我現在則在測試有沒有甚麼特定日期會導致有誤差的情況發生.
看來我的邏輯概念還太差了需要再加強..

作者: rszo 時間: 2013-8-27 09:44

回復 14# p212

剛測試了一下，好像在run的時候若是A與B列日期只相差1天的話
會變成:
A B C D
2013/7/31 2011/8/1 1年11個月 2
這情況是不是因為日期太接近所以小數點無限趨近於0.999999=1呢?

作者: p212 時間: 2013-8-27 11:21

本帖最後由 p212 於 2013-8-27 11:24 編輯

回復 16# rszo
1、YEARFRAC(start_date,end_date,basis)函數用以精準地計算兩個日期相差多少年(含小數)
其中basis係指所採用的日計利基的類型，若無特殊需求，建議basis採用0或省略即可。
請依您的需求選擇basis修改
basis-----------日計利基的類型
0或省略--------US (NASD) 30/360
1---------------實際天數/實際天數
2---------------實際/360
3---------------實際/365
4---------------歐洲 30/360

2、basis採用「1」可能較貼近您的需求，承13#之D3儲存格公式修改參考如下。
=IF(C3="","",IF(AND(DATEDIF(B3,A3,"y")>1,DATEDIF(B3,A3,"ym")=0),INT(YEARFRAC(B3,A3,1)-1),INT(YEARFRAC(B3,A3,1))))

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