amend VBA code to subtract 1 hour from working hours

[steve400243]

Hello, I use the below VBA code to generate working hours. I need it to subtract 1 hour from the total? Any thoughts?

Public Sub Worksheet_Change(ByVal Target As Range)


   Dim Cell As Range
   
   ' Calculate working hours if start and finish are both present
   For Each Cell In Target
      If (Cell.Column = [M3].Column Or Cell.Column = [N3].Column) And _
         (IsDate(Cells(Cell.Row, "M")) And IsDate(Cells(Cell.Row, "N"))) Then
         Cells(Cell.Row, "O") = WorkingHours.WorkingHours(Cells(Cell.Row, "M"), Cells(Cell.Row, "N"))
      End If
   Next Cell


End Sub

 

[cerfani]

What type of value does the WorkingHours function return? If it is just a number just subtract 1 :D

I assume it is more complicated but I have not used that function before. Is that something you made?

 

[steve400243]

Tried that method with no results. it returns a time value - 9:00 This was made for me to calculate working hours between a start and end time. between 8:00 - 5:00 normal work week.

 

[cerfani]

A time in excel is a date.

DateAdd("h", 1, WorkingHours.WorkingHours(Cells(Cell.Row, "M"), Cells(Cell.Row, "N")))

https://www.techonthenet.com/excel/formulas/dateadd.php

check the article