Salesforceの数式で個人的によく使うもののメモです。
日付型のあれこれ
現在の日付を取得する
現在の日付はTODAY関数で取得できます。
TODAY()
日付の加算/減算
日付型の加算と減算は日数単位です。
n日後の日付を取得したい場合はnを加算します。
日付型 + n
月数単位の加算や減算はADDMONTHS関数を利用します。
ADDMONTHS( 日付型 , n )
年数単位の加算や減算は月数単位の加算や減算の12倍になります。
ADDMONTHS( 日付型 , n * 12 )
日付から年月日を計算する
日付から年月日を計算するには、YEAR関数、MONTH関数、DAY関数を使います。
年を取得する場合は下記の形になります。
YEAR( 日付型 )
月を取得する場合は下記の形になります。
MONTH( 日付型 )
日を取得する場合は下記の形になります。
DAY( 日付型 )
年月日から日付を計算する
年月日から日付を取得するには、DATE関数またはDATEVALUE関数を使います。
DATE関数は年月日を数値として渡します。
DATE( YYYY , MM , DD )
DATEVALUE関数は年月日を文字列として渡します。
DATEVALUE( "YYYY-MM-DD" )
日付から任意の月の初日を計算する
DATE関数が年月日の数値を引数として日付型に直してくれるので、月初の取得はYEAR関数とMONTH関数を利用することで簡単に計算できます。
DATE( YEAR( 日付型 ) , MONTH( 日付型 ) , 1 )
nヶ月後の初日を計算するには、ADDMONTHS関数で月単位の加算を行います。
ADDMONTHS( DATE( YEAR( 日付型 ) , MONTH( 日付型 ) , 1 ) , n )
ADDMONTHS関数はマイナス値にも対応しているため、同じ数式でnヶ月前の初日を計算することができます。
日付から任意の月の末日を計算する
末日は初日と異なり30日や31日など一定ではないため、DATE関数で日を定数として扱えません。
そこで、次の月の初日から1日を引くという計算を行います。
ADDMONTHS( DATE( YEAR( 日付型 ) , MONTH( 日付型 ) , 1 ) , 1 ) - 1
初日と同様にnヶ月後やnヶ月前はADDMONTHS関数で調整できます。
ADDMONTHS( DATE( YEAR( 日付型 ) , MONTH( 日付型 ) , 1 ) , n + 1 ) - 1
日付から曜日を計算する
日付から曜日を計算する際にはWEEKDAY関数が使えます。
WEEKDAY( 日付型 )
WEEKDAY関数は曜日が数値として表現されるため、テキストで欲しい場合はCASE関数で分岐させることで綺麗に書けます。
CASE( WEEKDAY( 日付型 ) ,
1 , "日曜日" ,
2 , "月曜日" ,
3 , "火曜日" ,
4 , "水曜日" ,
5 , "木曜日" ,
6 , "金曜日" ,
7 , "土曜日" ,
"-"
)
日付から今日までの日数を計算する
日付間の日数は差分計算で計算できます。
TODAY() - 日付型
日付から今日までの月数を計算する
日付間の月数を計算するには、年月を月数に直した上で計算できます。
( YEAR( TODAY() ) - YEAR( 日付型 ) ) * 12 + ( MONTH( TODAY() ) - MONTH( 日付型 ) )
これで簡単な月数は計算することができますが、日付が同一月の場合に値に差異が生じることがあります。
これは、日数の大小関係でも月数が変わる場合（年齢等）に生じる差異のため、日数の大小判定を組み込む必要があります。
( YEAR( TODAY() ) - YEAR( 日付型 ) ) * 12 + ( MONTH( TODAY() ) - MONTH( 日付型 ) )
- IF( AND( MONTH( TODAY() ) = MONTH( 日付型 ) , DAY( TODAY() ) < DAY( 日付型 ) ) , 1 , 0 )
また、分かりにくくはなりますが、基準値を初日に移動させることで大小判定を無視することができます。（AND( MONTH( TODAY() ) = MONTH( 日付型 ) , DAY( TODAY() ) < DAY( 日付型 ) )がFALSEとなるため）
( YEAR( TODAY() - DAY( 日付型 ) + 1 ) - YEAR( 日付型 ) ) * 12
+ ( MONTH( TODAY() - DAY( 日付型 ) + 1 ) - MONTH( 日付型 ) )
日付から今日までの年数を計算する
日付間の年数を計算するには、月数とは打って変わり簡単な係数の商で計算できます。
ROUND( ( TODAY() - 日付型 ) / 365.2425 , 0 )
0.2425が余分に見えますが、閏年の補正で必要な余分です。
閏年の条件
(1) 西暦が4で割り切れる
(2) (1)の例外として、西暦が100で割り切れるかつ400で割り切れない年は平年となる
簡単に表現すると、「任意の連続した400年間のうち97年は閏年」となるため、400年間の日数を考えた場合は365 * 400 + 97日となります。
これを400年で平均化すると、1年は365.2425日となります。
時間型のあれこれ
現在の時間を取得する
現在の時間はTIMENOW関数で取得できます。
TIMENOW()
時間の加算/減算
時間型の加算/減算はミリ秒単位です。
h時間後、m分後、s秒後、msミリ秒後の時間を取得したい場合は単位に注意して加算します。
時間型 + ( ( h * 60 + m ) * 60 + s ) * 1000 + ms
時間から時分秒ミリ秒を計算する
時間から時分秒ミリ秒を計算するには、HOUR関数、MINUTE関数、SECOND関数、MILLISECOND関数を使います。
時を取得する場合は下記の形になります。
HOUR( 時間型 )
分を取得する場合は下記の形になります。
MINUTE( 時間型 )
秒を取得する場合は下記の形になります。
SECOND( 時間型 )
ミリ秒を取得する場合は下記の形になります。
MILLISECOND( 時間型 )
時分秒ミリ秒から時間を計算する
時分秒ミリ秒から時間を取得するにはTIMEVALUE関数を使います。
TIMEVALUE関数は時分秒ミリ秒を文字列として渡します。
TIMEVALUE( "HH:MM:SS.SSS" )
時分秒ミリ秒が数値として得られている場合はTEXT関数で文字列にする必要があります。
TIMEVALUE( TEXT( HH ) + ":" + TEXT( MM ) + ":" + TEXT( SS ) + "." + TEXT( SSS ) )
ミリ秒から時間を計算する
ミリ秒から時間を取得するには時分秒ミリ秒を計算した上でTIMEVALUE関数を使います。
TIMEVALUE(
TEXT( FLOOR( ms / 60 / 60 / 1000 ) )
+ ":" +
TEXT( FLOOR( ms / 60 / 1000 ) - FLOOR( ms / 60 / 60 / 1000 ) * 60 )
+ ":" +
TEXT( FLOOR( ms / 1000 ) - FLOOR( ms / 60 / 1000 ) * 60 )
+ "." +
TEXT( ms - FLOOR( ms / 1000 ) * 1000 )
)
これは、FORMATDURATION関数を使うと簡潔に表現できます。
TEXTVALUE( FORMATDURATION( FLOOR( ms / 1000 ) ) + "." + TEXT( ms - FLOOR( ms / 1000 ) * 1000 ) )
FORMATDURATION関数は引数に秒単位を必要とする点と戻値がHH:MM:SSとなることに注意が必要です。
時間の差分を計算する
時間の差分はミリ秒単位で計算されるため、単位の調整が必要な場合は係数をかける必要があります。
ミリ秒単位で取得する場合は下記のような形になります。
( 時間型 - 時間型 )
秒単位で取得する場合は下記のような形になります。
( 時間型 - 時間型 ) / 1000
分単位で取得する場合は下記のような形になります。
( 時間型 - 時間型 ) / 60 / 1000
時間単位で取得する場合は下記のような形になります。
( 時間型 - 時間型 ) / 60 / 60 / 1000
時間の差分を時間型で取得する場合は、TIMEVALUE関数を使う形で記述します。
TIMEVALUE(
TEXT( FLOOR( ( 時間型 - 時間型 ) / 60 / 60 / 1000 ) )
+ ":" +
TEXT( FLOOR( ( 時間型 - 時間型 ) / 60 / 1000 ) - FLOOR( ( 時間型 - 時間型 ) / 60 / 60 / 1000 ) * 60 )
+ ":" +
TEXT( FLOOR( ( 時間型 - 時間型 ) / 1000 ) - FLOOR( ( 時間型 - 時間型 ) / 60 / 1000 ) * 60 )
+ "." +
TEXT( ( 時間型 - 時間型 ) - FLOOR( ( 時間型 - 時間型 ) / 1000 ) * 1000 )
)
これもミリ秒から時間を計算することと同様なため、FORMATDURATION関数を使うことで簡潔に書くことができます。
TIMEVALUE( FORMATDURATION( FLOOR( ( 時間型 - 時間型 ) / 1000 ) ) + "." + TEXT( ( 時間型 - 時間型 ) - FLOOR( ( 時間型 - 時間型 ) / 1000 ) * 1000 ) )
日時型のあれこれ
現在の日時を取得する
現在の日時はNOW関数で取得できます。
NOW()
日時の加算/減算
日時型は整数部が日付で少数部が時間を表しています。
d日後、h時間後、m分後、s秒後、msミリ秒後の日時を取得したい場合は単位に注意して加算します。
日時型 + d + ( h + ( m + ( s + ms / 1000 ) / 60 ) / 60 ) / 24
月数単位の加算や減算はADDMONTHS関数を利用します。
ADDMONTHS( 日時型 , n )
年数単位の加算や減算は月数単位の加算や減算の12倍になります。
ADDMONTHS( 日時型 , n * 12 )
日時から日付を取得する
日時型から日付部分を取得する場合はDATEVALUE関数を使います。
DATEVALUE( 日時型 )
日時から時間を取得する
日時型から時間部分を取得する場合はTIMEVALUE関数を使います。
TIMEVALUE( 日時型 )
年月日と時分秒ミリ秒から日時を計算する
年月日と時分秒から日時を取得するには、DATETIMEVALUE関数を使います。
DATETIMEVALUE( "YYYY-MM-DD HH:MM:SS" )
DATETIMEVALUE関数はミリ秒の情報を付加できないため、ミリ秒の値を加える必要があります。
DATETIMEVALUE( "YYYY-MM-DD HH:MM:SS" ) + MS / 24 / 60 / 60 / 1000
日付と時間から日時を計算する
日付と時間が存在する場合は、年月日や時分秒ミリ秒を抽出するよりも、時間からミリ秒を欠落させて日時を計算する方が簡単になります。
DATETIMEVALUE( TEXT( 日付型 ) + " " + LEFT( TEXT( 時間型 ) , 8 ) ) + MILLISECOND( 時間型 ) / 24 / 60 / 60 / 1000
また、日付型のみをDATETIMEVALUE関数に渡すと日付T00:00:00.000Zになることを利用して、時間を別途加える方法もあります。
DATETIMEVALUE( 日付型 ) + ( HOUR( 時間型 ) + ( MINUTE( 時間型 ) + ( SECOND( 時間型 ) + MILLISECOND( 時間型 ) / 1000 ) / 60 ) / 60 ) / 24
日時の差分を計算する
日時の差分は整数部が日付で少数部が時間で計算されるため、単位の調整が必要な場合は係数をかける必要があります。
日単位で取得する場合は下記のような形になります。
( 日時型 - 日時型 )
時間単位で取得する場合は下記のような形になります。
( 日時型 - 日時型 ) * 24
分単位で取得する場合は下記のような形になります。
( 日時型 - 日時型 ) * 24 * 60
秒単位で取得する場合は下記のような形になります。
( 日時型 - 日時型 ) * 24 * 60 * 60
ミリ秒単位で取得する場合は下記のような形になります。
( 日時型 - 日時型 ) * 24 * 60 * 60 * 1000
差分を24時間未満の時間型で取得する場合は、TIMEVALUE関数を使う形で記述します。
TIMEVALUE(
TEXT( FLOOR( ( TIMEVALUE( 日時型 ) - TIMEVALUE( 日時型 ) ) / 60 / 60 / 1000 ) )
+ ":" +
TEXT( FLOOR( ( TIMEVALUE( 日時型 ) - TIMEVALUE( 日時型 ) ) / 60 / 1000 ) - FLOOR( ( TIMEVALUE( 日時型 ) - TIMEVALUE( 日時型 ) ) / 60 / 60 / 1000 ) * 60 )
+ ":" +
TEXT( FLOOR( ( TIMEVALUE( 日時型 ) - TIMEVALUE( 日時型 ) ) / 1000 ) - FLOOR( ( TIMEVALUE( 日時型 ) - TIMEVALUE( 日時型 ) ) / 60 / 1000 ) * 60 )
+ "." +
TEXT( ( TIMEVALUE( 日時型 ) - TIMEVALUE( 日時型 ) ) - FLOOR( ( TIMEVALUE( 日時型 ) - TIMEVALUE( 日時型 ) ) / 1000 ) * 1000 )
)
差分を日数を含めた形で文字列として取得する場合は、FORMATDURATION関数を使います。
FORMATDURATION( 日時型 , 日時型 )