[[20200502215435]]　『出勤時間を出したいです。』（らじ）　ページの最後に飛ぶ

『出勤時間を出したいです。』（らじ）

Private Sub Btn_Attnd_Create_Click()

    If Cmb_Year = "" Then
        MsgBox "年を正しく選択してください"
        Exit Sub
    End If
    If Cmb_Month = "" Then
        MsgBox "月を正しく選択してください"
        Exit Sub
    End If

    Dim Month_Start As Date
    Dim Month_End As Date
    Dim Wk_Year As Variant
    Dim Wk_Month As Variant
    Dim Year_Cnt As Long
    Dim Month_Cnt As Long
    Dim Month_day As Long

    Year_Cnt = Frm_Make_Calender.Cmb_Year.ListIndex
    Month_Cnt = Frm_Make_Calender.Cmb_Month.ListIndex
    Wk_Year = Frm_Make_Calender.Cmb_Year.List(Year_Cnt)
    Wk_Month = Frm_Make_Calender.Cmb_Month.List(Month_Cnt)

    Month_Start = DateSerial(Wk_Year, Wk_Month, 1)
    Month_End = DateSerial(Wk_Year, Wk_Month + 1, 1) - 1
    Month_day = Month_End - Month_Start + 1

    Dim i As Long, j As Long
    Dim Ctr As Long
    Dim Wk_Name() As String
    Dim ws As Worksheet
    Set ws = Worksheets("社員一覧")
    i = 6
    j = 0

    Ctr = ws.Cells(6, 3).End(xlDown).Row - ws.Cells(6, 3).Row
    ReDim Wk_Name(Ctr)

    For i = 6 To ws.Cells(6, 3).End(xlDown).Row
         Wk_Name(j) = ws.Cells(i, 3)
         j = j + 1
    Next

    Dim CurrentPath As String, SaveName As String
    CurrentPath = ActiveWorkbook.Path

    Dim Src As Workbook, Dst As Workbook
    Set Src = Workbooks.Open(Filename:=CurrentPath & "\勤怠.xlsm")
    SaveName = Wk_Year & "年" & Wk_Month & "月" & "勤怠.xlsm"
    Src.SaveAs Filename:=CurrentPath & "\" & SaveName
    Set Dst = Workbooks(SaveName)

    ThisWorkbook.Worksheets("社員一覧").Copy after:=Dst.Worksheets("Sheet1")

    Dim k As Long
    Dim EachName
    Dim DstSheet As Worksheet
    Dim Wk_Date As Date

    For Each EachName In Wk_Name
        Dst.Worksheets.Add after:=Sheets(Sheets.Count)
        Set DstSheet = Dst.ActiveSheet
        DstSheet.Name = EachName
        DstSheet.Cells(1, 1).Value = EachName

        DstSheet.Cells(1, 2).Value = "出勤"
        DstSheet.Cells(1, 3).Value = "退勤"

        DstSheet.Cells(1, 4).Value = "労働時間"
        DstSheet.Cells(1, 5).Value = "残業時間"
        DstSheet.Range(Cells(1, 1), Cells(1, 5)).HorizontalAlignment = xlCenter

        Wk_Date = Month_Start
        For k = 2 To Month_day + 1
            DstSheet.Cells(k, 1) = Wk_Date
            DstSheet.Cells(k, 1).NumberFormatLocal = "m""月""d""日"""
            DstSheet.Range(Cells(k, 2), Cells(k, 3)).Locked = False
            DstSheet.Cells(k, 4).FormulaR1C1 = "=IF(RC[-2]="""","""",CEILING(RC[-2],""0:15""))"
            DstSheet.Cells(k, 5).Formula = "=IF(RC[-2]="""","""",FLOOR(RC[-2],""0:15""))"
            DstSheet.Range(Cells(k, 2), Cells(k, 5)).NumberFormat = "h:mm"
            DstSheet.Range(Cells(k, 2), Cells(k, 5)).HorizontalAlignment = xlCenter
            Wk_Date = Wk_Date + 1

        Next
        Application.ReferenceStyle = xlA1
        DstSheet.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
        DstSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    Next

    Worksheets("Sheet1").Activate
    Range("A1").Activate
    Dst.Save
    Dst.Close
    Set DstSheet = Nothing
    Set Dst = Nothing

End Sub

＜使用 Excel：Office365、使用 OS：Windows10 ＞

のマクロで労働時間の列に
退勤時刻-出勤時刻-(5時間以上の勤務時間で75分)
残業時間の列に
退勤時刻-出勤時刻-(5時間以上の勤務時間で75分)-7時間45分
を出したいです。
まったくもってわからないので教えてもらえないでしょうか。

（らじ） 2020/05/02(土) 22:00

 ＞ DstSheet.Cells(k, 4).FormulaR1C1 = "=IF(RC[-2]="""","""",CEILING(RC[-2],""0:15""))"
 ＞ DstSheet.Cells(k, 5).Formula = "=IF(RC[-2]="""","""",FLOOR(RC[-2],""0:15""))"
      ↓
    DstSheet.Cells(k, 4).FormulaR1C1 = "=IF(COUNT(RC[-2]:RC[-1])<2,"""",FLOOR(RC[-1],""0:15"")+(RC[-1]<RC[-2])" & _
                                       "-CEILING(RC[-2],""0:15"")-(FLOOR(RC[-1],""0:15"")+(RC[-1]<RC[-2])" & _
                                       "-CEILING(RC[-2],""0:15"")-""4:59:59"">=0)*""1:15"")"

    DstSheet.Cells(k, 5).FormulaR1C1 = "=IF(RC[-1]="""","""",MAX(0,RC[-1]-""7:45""))"

（半平太） 2020/05/03(日) 00:23

コメント返信：

[ 一覧(最新更新順) ]

[[20200502215435]] 『出勤時間を出したいです。』（らじ） ページの最後に飛ぶ

[[20200502215435]]　『出勤時間を出したいです。』（らじ）　ページの最後に飛ぶ