Excel:日付を四半期に変換する

暦年

の場合、会計年度が12月31日に終了する場合は、短い式を使用できます。 (図1は、日付を年、月、週、または平日に変換するために使用できるExcel関数を示しています。 最初に、=MONTH（）関数を使用して、日付を月の番号に変換します。 その結果を3で割ります。 1月の日付は、0.33、2月0.67、3月1.00、4月1.33などを返します。 =ROUNDUP()関数を使用すると、1月、2月、3月には1、4月から6月には2などのようになります。

四半期を「Q1」または「Qtr1」として表示するには、結果の前に適切なテキストを連結します。 図2のセルA7では、=”Q”&ROUNDUP(MONTH(A2))の式が使用されています。)/3,0)&”-“&年（A2）に戻ります”Q1-2017。”

その他の会計年度については、

月31を終了する会計年度は、小売企業では一般的です。 政府の請負業者は、9月30日の会計年度末を使用する場合があります。 会計四半期を計算するには、最初に=MONTH()を使用して日付を月数に変換します。 この結果は、1から12までの12個の整数に制限されます。VLOOKUP式を使用して、月の数値を四半期の数値に変換できます。 しかし、式=VLOOKUP(MONTH(A2),{1,4;2,1;5,2;8,3;11,4},2) 覚えて入力することは困難です。 中括弧内の配列定数には、5行×2列のルックアップテーブルが保持されます。

代わりに、=CHOOSE()関数を使用すると、月1から12に対応する12個の四半期番号を指定できます。 会計年度が1月に終了する場合は、=CHOOSE(MONTH(A2))を使用します),4,1,1,1,2,2,2,3,3,3,4,4). 月に終了する会計年度の場合は、=CHOOSE(MONTH(A2))を使用します),4,4,4,1,1,1,2,2,2,3,3,3).

四半期を1ではなくQ1として表示したい場合は、アンパサンドを使用してCHOOSE関数の前に”Q”を連結することができます。 結果は、図2のセルA13およびA20に示され、式はD13およびD20に示されています。

四半期に会計年度を追加する

一部の企業では、会計年度は年の12月が発生する暦年によって決定されます。 たとえば、2017年2月から2018年1月までの会計年度には、Q1-2018からQ4-2018と呼ばれる四半期があります。 他の企業では、会計年度は”17-18″のようなものと呼ばれることがあります。”

会計年度が終了月の年である場合は、IF()関数を使用して、月の番号が開始月よりも小さいかどうかをテストします。 たとえば、会計年度が1月で終わる場合、日付が2月より前の場合はYEAR(A2)、2月以降の場合はYEAR(A2)+1になります。 したがって、会計年度を四半期に含めるには、=IF(MONTH(A2)<2,0,1)+YEAR(A2)を使用できます。

3月に終わる年の場合、<2テストを<4:=IF(MONTH(A2)<4,0,1)+YEAR(A2)に変更します。 9月に終わる1年の場合、<10:=IF(MONTH(A2)<10,0,1)+YEAR(A2)をテストします。図2のセルA21は、次の長い数式を使用して会計四半期と年度を連結する方法を示しています。=”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-“&IF(MONTH(A17)<2,0,1)+YEAR(A17). これにより、四半期は「Q4-2017」の形式で返されます。”

代わりに、17-18と言う必要がある場合、長い式はさらに長くなります。 数式の年の部分を取得するには、次のようにします: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-“&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-“&MOD(YEAR(A2)+1,100)).

月の代わりに期間を返す

私は最近、ナッシュビルでIMA®のためのセミナーを実施しました。 テネシー州政府は6月に会計年度を終了しているので、彼らは月の代わりに期間番号を使用して報告することを好みました。 7月は期間1、12月は期間6、1月は期間7というようになります。 この場合、多くの同僚がデータの右側にあるVLOOKUPテーブルを使用しました。 しかし、彼らはまた使用することができます=CHOOSE(MONTH(A2),7,8,9,10,11,12,1,2,3,4,5,6) 同じ結果を達成するため。

日付はテキストにすることはできません

これらの数式のすべての注意点は、日付をテキストではなく実際の日付として保存していることです。 チェックする簡単な方法：CtrlキーとGrave Accentキー（`）を押して、数式を表示モードに切り替えます。 実際の日付は、42783などのシリアル番号に変換されます。 それでもテキストの日付が表示されている場合、数式は機能しません。 Ctrl+`をもう一度押して、数式の表示を終了します。

最近、マネージャーが日付を1st Feb2017、2nd Feb2017、3rd feb2017、4th feb2017として表示したいワークシートを見つけました。 日付の後の接尾辞（”st”、”nd”、”rd”、および”th”）は、基数を序数に変換します。 Excelの塗りつぶしハンドルを使用すると、基数の列を簡単に作成できますが、この記事の数式ははるかに複雑になります。 テキストの日付を処理するために、41文字の数式が2,438文字に増加しました(図3参照)。