時間の計算が分かりません

質問

時間の計算で、B列にいれる式が全く分からなくて困っています。
A列が
空白だったら”空白”
7:30以下だったら”空白”
8:00以上だったら”0:30”
7:31～7:59の間だったら"7:30を超えた0:30未満

=IFS(A1="","",A1<="7:30","",A1>="8:00","0:30"にANDやMAXなどを組み合わせてみたのですが出来ません。
教えてください。
宜しくお願い致します。

回答No.2

SI299792 · ベストアンサー

=IF(A1>"7:30"+0,MIN(A1-"7:30","0:30"),"")

又は
=MAX(MIN(A1-"7:30","0:30"),0)
表示形式、ユーザー定義｢h:mm;;｣

Excel は空白を 0とみなすので、空白の判断は不要です。
（但し、関数等で空白にした場合は 0とみなされません）

お礼

SI299792さん、教えてくださり大変有難うございました。
簡潔な式でできること、目からウロコでした。
そして、ユーザー定義に ;; を使うとどうなるか、知らなかったので勉強になりました。

A列の空白の部分に実際は式が入っていたので、SI299792さんが教えてくださった式の前に=IF(A1="","",を追加しました。
そうすると、=MAXで始まる式はユーザー定義｢h:mm;;｣に変更しなくても良かったので、一番簡潔な下段のMAXを採用することにしました。

助かりました。
ありがとうございます。

回答No.4

NuboChan

とりあえずVBAで作ってみました。

A列の範囲は、適当です。

Option Explicit
'A列が空白だったら "空白"
'7:30以下だったら”"空白"
'7:31～7:59の間だったら"7:30を超えた値で0:30未満
'8:00以上だったら”0:30”

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Intersect(Target, Range("A1:A100"))

If Target Is Nothing Then Exit Sub

Dim c As Range

For Each c In Target
Select Case c.Value
Case TimeValue("07:31") To TimeValue("08:00")
c.Offset(, 1).Value = c.Value - TimeValue("07:30")
Case Is < TimeValue("8:01")
c.Offset(, 1).Value = ""
Case Else
c.Offset(, 1).Value = TimeValue("00:30")
End Select
Next
End Sub

お礼

時間を割いて考えてくださり有難うございます。
VBA、私は全く分かりません。
しかしNuboChanさんが丁寧に記載してくださったこと、とても感謝です。
有難うございました。

回答No.3

imogasi

＜考え方＞
・エクセルのバージョン IFS関数が使えるバージョンか 質問に書いてないが問題。
　ーー＞関数式の書き方が変わる。
・時刻シリアル値の「リテラル値」（具体的な時刻）の関数の引数内での表現方法
”8:30"のように、””で囲み、「：」（半角）で時と分を分けて表す。基本であることは間違いな　　い。
・関数式内で比較を書いていく順序
特殊な事例を最初に 例　空白セルのケース
次に「数直線」的な時系列を考えて並べて、、比較の式の順序を考える。範囲にしないなら、基本は右から（数値的に大から）かな？

―ーーー｜―ーーー｜―ーーー｜―ーーー｜―ーーー｜
7:30　 8:00 　　　9:00 　　10:00
この順序で比較演算子「＜」や「＞＝」など　を使って式を書く
=IF(セルの値＝番地＜（＝比較演算子）区切り値１、該当の場合、(該当しない場合)・・・のように。
＜本件について＞
IFSを使わないでやった。時間間隔の比較に徹底してない。
例データ　質問で掲示の例　A２：B１１　便宜上B列は関数の結果を表示している。
A列　　B列
7:30:00
10:00:00 0:30
5:30:00

7:31:00 0:01
8:00:00 0:30
7:25:00
7:47:00 0:17
9:20:00 0:30
7:59:00 0:29
ーー
B2の関数　=IF(A2<="7:30"*1,"",IF(AND(A2>="7:31"*1,A2<="7:59"*1),A2-"7:30"*1,"0:30"*1))
　*1としている部分に要注目。
下方向に式を複写
ーー
B列の表示形式を、（時刻の）ユーザー定義の「h:mm」の設定。
ーー
結果　上記B列　ーー＞質問と合致。
＝＝＝＝
長年エクセル関数に関心を持っているつもりだったが、手こずった。
式で、＊１の箇所で。入れないと、うまく行かない。
　でも最適な方法かどうか自信がない。

お礼

imogasiさん、丁寧に説明してくださり本当に有難うございました。
基本は右から（数値的に大から）、そして　*1、
分かりやすく教えていただき、とても勉強になりました。

回答No.1

f272

=IFS(A1="","",A1>="8:00","0:30",A1>"7:30",A1-"0:30",A1<="7:30","")

お礼

考えてくださり大変ありがとうございました。