仕様に負けるな！時間の書式がドット区切りでも計算してみせる！

職場のシステムは往々にしてクソ仕様を抱えている
まず書式をどうにかしてコロン（:）区切りにしたい
文字列の数値化、時間の丸め処理

職場のシステムは往々にしてクソ仕様を抱えている

奥さん、ちょっと聞いてくださらない？うちの会社の勤怠Webシステム、労働時間がドット区切りで表示されるのよ。

こちらがそのイメージ画像。出退勤の時刻は日時書式なのに労働時間がドット区切りとはどいうことだ。これじゃただの小数じゃん、時間じゃないじゃん！
これのややこしいところは、書式が小数なのに数字は時間ってところです。何言ってるか自分でもわかりにくいと思ってるけど、要するに総労働時間の「8.10」は「8時間10分」を示しているのであって、「8.1時間」ではないということ。

ただ表示がこうなってるってだけならまあいい。だがまさやんは業務上、この総労働時間に当たる部分を複数人数分足して、事業所ごとの総労働時間をはじき出さなくてはならなかった。1事業所に多いところで20人くらいいるし、事業所自体10近くあるのよ。
この計算を、先輩方は暗算していた…。見た目小数なのに実質時間だから、小数点以下（便宜上こう呼ぶ）を60繰り上げで暗算…。さらに計算結果の小数点以下（便宜上…）を30分切り上げ未満切り捨てで整数に丸める。

すごいよ先輩…。熟練するとできるようになるんだ…。純粋に感動したけど正直まさやんはやりたくない…。どうにかして数値をコピってパソコンに計算させたい。

そこで画面についてたExcel形式のデータダウンロードボタンを試してみた。ワンチャン、中身のデータは時間書式になっているのでは…！？

どきどきしながらファイルを開く…。

結果を発表します！ 所定労働時間8、残業0.1、総労働時間8.1でした！！

Non！そういうことではないのだよ！！！

ドット区切りのままだったし、なんだったらおしりの0が消えてるではないですか！これじゃますます計算できない。先輩方が暗算を極める道を選んだ理由がわかる。

一体どこの誰がこんな仕様を許したんだ…。呪詛を吐きたいまさやんだったが、やはり暗算はしたくない。どうにかしてこの試練を乗り越えなくてはならないのだった…。

まず書式をどうにかしてコロン（:）区切りにしたい

時間表示にするには「.」と「:」の置き換えが必須と思われた。表計算ソフトには置換のための関数が備わっている。今回の場合であれば直感的に入れ替えられるSUBSTITUTE関数か…。ただし、SUBSTITUTE関数は文字列操作用だ。「8.10」は数値なので直接扱うことができない。TEXT関数で事前に数値を文字列に変換しておく必要がありそうだ。

TEXT関数

=TEXT(値,表示形式)

数値に指定した書式を設定し、文字列に変換した結果を返します。

値	数値、結果が数値となる数式、または数値が入力されているセルへの参照を指定します。
表示形式	[セルの書式設定]ダイアログボックスの[表示形式]タブの[分類]ボックスに表示されている数値形式を、文字列として指定します。

整数部2桁、小数部2桁の文字列にしておけば、おしりの0が消える問題も解決できるはず。そのための表示形式は“00.00”です（数値に書式をあてる場合、0で指定した桁は無くても0詰めで表示されるようになります）。

つまり、A2セルに変換させたい数値がある場合、以下のように入力する。

=TEXT(A2,"00.00")

実際に試してみるとこうなります。
「3」も「03.00」になっていい感じ。
総労働時間は数値なので右寄せになってるのに対し、TEXT関数を使ったセルは文字列に変換されたので左寄せになっています。

続いて、SUBSTITUTE関数で「.」を「:」に入れ替えます。

SUBSTITUTE関数

=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])

文字列中の指定した文字列を新しい文字列で置き換えます。

文字列	置き換える文字列を含む文字列、または目的の文字列が入力されたセル参照を指定します。
検索文字列	置き換え前の文字列を指定します。検索文字列と置換文字列の大文字小文字の表記が異なる場合、文字列は置換されません。
置換文字列	置き換え後の文字列を指定します。
[置換対象]	文字列に含まれるどの検索文字列を置換文字列に置き換えるかを指定します。省略された場合は、文字列中のすべての検索文字列が置き換えの対象となります。 ※何個めの検索文字列を置き換えるか数で指定します。

B列の文字列を変換してC列に出力する場合、以下のように入力します。

=SUBSTITUTE(B2,".", ":")

見た目時間の表記になりました！

あとは計算できるように、この文字列をシリアル値に転換できればよいです！

シリアル値については以下の記事を参照ください。

文字列の数値化、時間の丸め処理

数値に転換できる文字列の数値化はVALUE関数で行います！
時間の場合TIMEVALUE関数も使えますが、24時間以上の表示ができないので基本VALUE関数を使います。

VALUE関数

=VALUE(文字列)

数値を表す文字列を数値に変換します。

文字列	文字列を半角の二重引用符 (“) で囲んで指定するか、または変換する文字列を含むセル参照を指定します。

C列の文字列をD列に数値化して出力する場合、以下のように入力します。

=VALUE(C2)

どうですか！
無事に数値化されました！

表示が小数になっちゃいましたが、これはセルの書式設定を時間に指定すれば解決します。

ほらね！

ここまで3つの関数を使って列をずらしながらひとつずつ進めてみたけど、これだと邪魔なので一列で一気にやってみます。一度に3つの関数を組み込んでB列に出力する場合、以下のように入力します。

=VALUE(SUBSTITUTE(TEXT(A2,"00.00"),".",":"))

すっきりしました！

あとはこれらの総労働時間数を足して、事業所全体の労働時間を出したいです。労働時間はすでに適切に数値化されているので合計を出すだけならSUM関数でいけます。

SUM関数

=SUM(数値1,[数値2,…])

セル範囲に含まれる数値をすべて合計します。

数値1	合計する最初の数値または範囲です。論理値や文字列は無視されますが、引数として入力されていれば計算の対象となります。
[数値2，…]	合計する数値または範囲を指定します。論理値や文字列は無視されますが、引数として入力されていれば計算の対象となります。省略した場合、結果には数値1が返されます。

D2に計算結果を出力してみます。

=SUM(B2:B9)

無事に計算できました！

結果に少ない時間が表示されていたら、セルの書式を [h]:mm に変更してみてください。

ついでに、30以上分切り上げ未満切り捨てて整数に整える丸め処理もしてしまいます。今回のような時間の四捨五入的丸め計算はMROUND関数を用いると簡単です。MROUND関数は時間の計算用ではないですが、数値を倍数で割った余りが倍数の半分以上である場合0から遠い方の値に丸め（切り上げ）られるので、30分を基準として切り上げ切り捨てをする場合便利です。

MROUND関数

=MROUND(数値,倍数)

目的の倍数に丸められた数値を返します。

数値	切り上げあるいは切り捨ての対象となる数値を指定します。
倍数	切り上げあるいは切り捨てされた数値が、その倍数となるような数値を指定します。

D2の結果を処理する場合、次のように入力します。