コロンなしで時間を入力し (hhmmss)、Excel で時間差を計算します。

Question

コロンなしで時間を入力しhhmmss、別のセルとの時間差を計算したいです。たとえば、あるセルに開始時間を入力し、別のセルに終了時間を入力します。次に、時間差を計算する必要があります。ただし、時間、分、秒を含める必要があります。

問題は、コロンなしで時刻を入力したいが、セルではコロン付きで表示したいという点です。これを実現するために、カスタム数値形式でセルをフォーマットしました00\:00\:00。

Answer 1

一つの選択肢としては、セルの書式設定とExcelを組み合わせて使うことです。時間機能。

時間入力セル (この例では A2 と B2) を としてフォーマットしますText。 期待されるフォーマットは常に なのでhhmmss、1 桁の時間の場合は先頭のゼロを入力します。 次に、次の数式を使用して計算します。

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

これは、左端の 2 文字を「時間」、中央の 2 文字を「分」、右端の 2 文字を「秒」として取得し、Excel が時間として認識するものに変換します。次に、一方から他方を減算し、結果を次の形式で表示しますhhmmss。

編集: 要件が質問で指定されているとおりではないため、先頭のゼロを考慮して数式を修正し、次のようにパディングしました。

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

非常に読みにくくなりましたが、値にゼロが埋め込まれ、右端の 6 が使用されるようになったため、使用するゼロの数に関係なく機能するはずです。

hh:mm:ssこの場合のように、実際には結果を特別なフォーマットにしたいと思うと思います。

Answer 2

入力したとおりに時間をデコードする別の方法は次のとおりです。

アクションをわかりやすくするために、開始時間と終了時間を書式なしの数値のままにしました。この例では、開始時間は午前 0 時または正午の 25 秒後なので、000025 と入力します。数値としては 25 として保存されますが、書式設定により 00:00:25 のように見えます。

このアプローチでは、100 の累乗の数値の位置に基づいて時間と分を切り離します。秒は常に右の 2 桁になります。C2 の式は次のとおりです。

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

MOD 関数は INT 関数の逆で、割り算後の余りを返します。

Answer 3

これは古い投稿であることは承知していますが、(私と同じように) 人々が数え切れないほどのフォーラムで、タイムシートへの入力時間を節約し、手動で「コロン」を入力する必要がなく、コロンが表示され、計算を実行できるソリューションを探しているのであれば、VBA スクリプト、非表示のセル、数式などを使用せずに、これが私が思いついたソリューションです。これは、先頭のゼロ、24 時間制、および深夜にまたがるタイムシートでも機能するようです (ただし、タイムシートが複数の深夜にまたがることが予想される場合は調整が必要です。私の場合は、これは必要ないと思いました)。

また、「Seconds」は必要ありませんが、次の概念に従えば簡単に拡張できるはずです。

開始時刻と終了時刻のセルを次のようにカスタム書式設定します。

0#":"##

これにより、それらは「テキスト セル」に変換されますが、数字が埋め込まれて常に 4 桁の数字が表示されます (先頭に 0 があっても、またはアメリカの友人の場合は 0 があっても)。

注意: これにより、任意の4桁の数字を時間形式として入力できるようになります。したがって、理論的には0768と入力して、07:68という時間になることが可能ですが、もちろんこれは存在しません。したがって、

a) 次のようにデータ検証リストを作成します。 - 別のワークシート (タブ) の 1 つの列に、許容されるすべての値のリストを作成します。私の場合は、15 分単位のみを扱っていたため、0500、0515、0530、0545...0445 でした。秒単位までドリルダウンする必要がある場合 (本当に?)、これは非常に長くなります...つまり、050001、050002... などです。または、このリストを作成するためだけに別の数式を記述し、テキスト データ (値のみを貼り付け) を新しい列にコピーして数式を削除することもできます (私はそうします)。 - 「許容値」のセルをすべて選択し、ツールバーの「数式」タブで「名前の定義」を選択します。「MonkeyTimeList」のような名前を付けます。 - タイムシートのワークシートに戻り、開始時間と終了時間の両方を含むすべてのセルを選択し、ツールバーの「データ」タブに移動して「データの入力規則>データの入力規則>設定」を選択し、「許可」セクションでドロップダウンから「リスト」を選択し、「ソース」セクションで「=」に続けて定義した名前を入力します。私の場合は「=MonkeyTimeList」です。「OK」をクリックする前に、「エラー アラート」タブを選択し、「エラー アラートを表示...」ボックスにチェックを入れ、「スタイル」ドロップダウンで「停止」を選択します。「Monkey Man はあなたがバカだと言っています。正しい形式で時間を入力してください!」のようなエラー メッセージを自由に追加できます。次に「OK」をクリックします。 - これにより、「許容可能な」リストのデータのみが時間として入力できるようになります。

b) 理論的には、条件付き書式を使用して、誤ったデータが追加されたセルを強調表示することができます。これにより、データ検証データ用に別のワークシートを使用する必要がなくなります。私はまだこの方法を実行したことがないので、理論的とだけ言っています。これは別のチュートリアルになるかもしれません...

これで、毎回コロンやシフトキーを探すことなく時間を入力できるようになりました。

セルの書式に秒数を本当に含めたい場合は、カスタム セルの書式は次のようになります。

0#":"##":"##

それで......開始時刻から終了時刻までの時間を計算します....繰り返しますが、これは分のみに基づいています....ただし、ロジックを理解できれば、秒にも拡張できます。

3 番目の列 (列 A が開始時刻、列 B が終了時刻、列 C が合計時間であると仮定) に、次の数式を入力します。

=IF(VALUE(B2)>=VALUE(A2),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),VALUE(MID(TEXT(B2,"0000"),3,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),24,VALUE(MID(TEXT(B2,"0000"),3,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)))

さて、説明しましょう...

「IF」関数は、24 時間制で午前 0 時前と午前 0 時後の終了時間を区別します。午前 0 時以降の場合には、終了時間に 24 時間が追加され、終了時間から開始時間を引いた計算が機能するようになります。午前 0 時前の場合には、24 時間を追加する必要はありません。したがって、計算上、0100 は実際には 2500、0200 は 2600 として扱われます。注意: これは、シフトが 23 時間 45 分以内に実行されることを前提としています。シフトがそれ以上実行される場合は、数式に日付列を組み込む必要があるかもしれません... 繰り返しますが、私はその必要はありませんでした。

MID 関数は TEXT (つまり TEXT 関数) で動作し、参照されたセルの特定の位置にある数字を指定された形式で返します。つまり、「MID(TEXT(B2,"0000"),1,2)」はセル B2 を参照し、常に 4 桁/文字の形式で参照し、位置「1」から始まる 2 桁を返します。私の場合、これは時間全体を表します。別の例: 「MID(TEXT(A2,"000000"),5,2)」はセル A2 を参照し、位置「5」から始まる 2 桁を返します。これは秒全体である可能性があります。

返されたテキストを数値に変換し、計算で使用できるようにするために、これらすべての前に「VALUE」関数を配置しました。

これにより、各セルで時間、分、秒を別々に扱うことができます。時間はすでに整数なので、計算は簡単です。ただし、分は実際には時間の分数、または 60 分の分数であるため、「分」を表す数字を抽出したら、「60」で割って 1 時間の「分数」を取得します。同様に、秒についても、3600 (1 時間の秒数) で割って、「1 時間の分数」としての秒を取得します。

時間が分数付きの数字として表されるようになったので、好きなように加算または減算できます。私の数式の場合、開始時刻と終了時刻の間の合計時間数が表示されます。数式を含むセルをカスタム > 00.00 としてフォーマットすると、時間給などで乗算できるようになります。

もし誰かがまだ自分のレシピに2回目を追加したいと思っていて、行き詰まっているなら、私に知らせてください。仕事で退屈していたら、手伝うことができるかもしれません...でも、2回目って本当ですか？