[[20161020200050]]　『時間計算を教えてください』（ぺぺ）　ページの最後に飛ぶ

『時間計算を教えてください』（ぺぺ）

       Aに開始時間      Bに終了時間           Cに日付が変わるまでに動いた時間                      を書く欄が有るとします。
       10/6 2:00              10/6 6:00       6日 17:30     
       10/6 8:00              10/6 13:30      7日 5:00
       10/6 15:50            10/6 23:50       8日 6:00
       10/7 18:00            10/7  22:00      9日
       10/7 23:00            10/8  1:00        
       10/8  3:00             10/8  8:00

その横にaとbの時間を日付別に働いた時間をexcelで出したいのですが数式をよろしくお願いします。

aとbの日付と一緒に時間を入力してます。

＜使用 Excel：unknown、使用 OS：unknown ＞

 「数式で、C列に」でしたか。

 済みません、私の手に負えません。　m(__)m

 識者のレスをお待ちください。

（半平太） 2016/10/21(金) 08:09

 御大　半平太さんですら敬遠されるテーマですから、素人のβが　関数による処理の回答なんぞ
 できるわけがなく、交通整理だけ。

 １つのセル内の　10/6 2:00 といった値ですけど、これは　実際には日付型のデータで、表示書式で
 10/6 2:00 といったように表示させていると考えていいですね？
 （標準書式のセルに打ち込むと日付型データになりますので）

 それとも、本当に、「文字列」として　10/6 2:00 と入っているのでしょうか？

（β） 2016/10/21(金) 08:55

式を簡単にするため、C列は日付を予め手入力しておき、D列に合計時間を計算するというのではいかがでしょう？
更に、E～H列を作業列として使う例です。

D2: =SUMIF(E:E,C2,F:F)+SUMIF(G:G,C2,H:H)
E2: =INT(A2)
F2: =MIN(B2,DATE(YEAR(A2),MONTH(A2),DAY(A2)+1))-A2
G2: =INT(B2)
H2: =IF(INT(A2)<>INT(B2),B2-INT(B2),"")

	A		B		C	D	E	F	G	H
1	開始時間	終了時間	日	時間	wk1	wk2	wk3	wk4
2	10/6 2:00	10/6 6:00	6日	17:30	6日	4:00	6日	
3	10/6 8:00	10/6 13:30	7日	5:00	6日	5:30	6日	
4	10/6 15:50	10/6 23:50	8日	6:00	6日	8:00	6日	
5	10/7 18:00	10/7 22:00			7日	4:00	7日	
6	10/7 23:00	10/8 1:00			7日	1:00	8日	1:00
7	10/8 3:00	10/8 8:00			8日	5:00	8日	
（???） 2016/10/21(金) 09:13

ちなみに、全列について、日付型を元にした、カスタムの条件付き書式を設定してください。例えば、C2セルの値は2016/10/6。
A,B列も、きっとそうやって条件付き書式を設定し表現していますよね？
（???） 2016/10/21(金) 09:20

Sub main()'ご参考
'Sheet1が対象シート

    Dim dt() As Long, c As Range, mi As Date, t As Date, i As Long
    With Sheets("Sheet1")
        .Columns("C").ClearContents
        .Columns("C").NumberFormatLocal = "G/標準"
        mi = Application.WorksheetFunction.Min(.Range("A1").CurrentRegion)
        x = Day(Application.WorksheetFunction.Max(.Range("A1").CurrentRegion)) - Day(mi)
        ReDim dt(x)
        Set rg = .Range("A1").CurrentRegion.Resize(, 1) 'Select
        For Each c In rg
            For t = c.Value To c.Offset(, 1).Value Step 1 / 1440
            dt(Day(t) - Day(mi)) = dt(Day(t) - Day(mi)) + 1
            Next t
        Next c
        For i = 0 To x
            .Range("C" & i + 1).Value = Day(mi + i) & "日　" & Int(dt(i) / 60) & ":" & Format(dt(i) - 60 * Int(dt(i) / 60), "00")
        Next i
    End With
End Sub
（mm） 2016/10/21(金) 10:02

Sub main2() '複数月対応
'Sheet1が対象シート

    Dim dt() As Long, c As Range, rg As Range, t As Date
    Dim dic, k
    With Sheets("Sheet1")
        .Columns("C").ClearContents
        .Columns("C").NumberFormatLocal = "G/標準"
        Set dic = CreateObject("Scripting.Dictionary")
        Set rg = .Range("A1").CurrentRegion.Resize(, 1)
        For Each c In rg
            For t = c.Value To c.Offset(, 1).Value Step 1 / 1440
            dic(Format(t, "yyyy/m/d")) = dic(Format(t, "yyyy/m/d")) + 1
            Next t
        Next c
        Set rg = .Range("C1")
        For Each k In dic.keys
        rg.Value = Format(k, "m/d日") & Space(1) & Int(dic(k) / 60) & ":" & Format(dic(k) - 60 * Int(dic(k) / 60), "00")
        Set rg = rg.Offset(1)
        Next k
    End With
End Sub
（mm） 2016/10/21(金) 11:41

 関数ご希望で、以下はお呼びじゃないでしょうが、ＶＢＡ案がアップされているので、付和雷同（？）で。
 2行目からデータがあるという想定のコードです。

 Sub Test()
    Dim sl As Object
    Dim c As Range
    Dim i As Long
    Dim f As Date
    Dim t As Date
    Dim d As Variant

    Set sl = CreateObject("System.Collections.SortedList")
    Range("A1").CurrentRegion.Offset(1).Columns("C").ClearContents

    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        f = c.Value
        t = c.Offset(, 1).Value

        If f < t Then '念のため

            Do
                If Int(f) = Int(t) Then
                    sl(Int(f)) = sl(Int(f)) + Hour(t - f) * 60 + Minute(t - f)
                    Exit Do
                Else
                    sl(Int(f)) = sl(Int(f)) + Hour((Int(f) + 1) - f) * 60 + Minute((Int(f) + 1) - f)
                    f = Int(f) + 1
                End If
            Loop

        End If
    Next

    For i = 0 To sl.Count - 1
        d = sl.getbyindex(i)
        Range("C2").Offset(i).Value = Format(sl.getkey(i), "d""日 """) & d \ 60 & ":" & Format(d Mod 60, "00")
    Next

 End Sub

（β） 2016/10/21(金) 14:16

 	C	D	E	F
1	10/6	10/7	10/8	10/9
2	4:00	0:00	0:00	0:00
3	5:30	0:00	0:00	0:00
4	8:00	0:00	0:00	0:00
5	0:00	4:00	0:00	0:00
6	0:00	1:00	1:00	0:00
7	0:00	0:00	5:00	0:00

	17:30	5:00	6:00	0:00　　← 合計

 C1から右に日付を入力しておく

 A・B列2行目からデータだとして

 C2 =IF(COUNT($A2:$B2)<2,"",MAX(0,MIN($B2,C$1+1)-MAX($A2,C$1)))

 表示形式「時刻」にして、右下コピー

 各列を SUM で合計

 参考まで。
（笑） 2016/10/21(金) 22:50

コメント返信：

[ 一覧(最新更新順) ]

[[20161020200050]] 『時間計算を教えてください』（ぺぺ） ページの最後に飛ぶ

[[20161020200050]]　『時間計算を教えてください』（ぺぺ）　ページの最後に飛ぶ