Macro

[Excelexcel86]

Hi guys I’ve had help with a formula to calculate certain times but on the advice of another member he recommends that I might need a macro so the issue is I have 4 cells which I will show example below I need a macro assigned to the one cell that is labelled time inside warehouse that calculates from when the item came in the warehouse to when it left the warehouse now the work hours are 06:00 to 14.30 Monday to Thursday and 6:00 to 11:00 on a Friday it also needs to calculate the hours if it goes into another day that is in the start time

Time entered warehouse	time exited warehouse	time inside warehouse for work hours	Total time
01/03/2023 07:30	01/03/2023 13.30	Should be 6 hours as it’s inside work hours
01/03/2023 09.00	02/03/2023 08:00	Should be be 7:30 hours as it has gone over to the next day and into start time again	Should be 23 hours
10/03/2023 08:00	10/03/2023 12:00	This is a Friday so should be 3hrs as finish at 11:00	Should be 4 hours

 

[Excelexcel86]

Ok when I get bk I will send you what I have done

 

[Excelexcel86]

THIS IS WHAT THE TABLE LOOKS LIKE BUT STILL NOT WORKING

 

[Excelexcel86]

Ok when I get bk I will send you what I have done

THIS IS THE MACRO

 

[Anthony47]

What is wrong and why?

 

[Anthony47]

Probably you meant the it is incorrect that both start time 7:30 and 7:31 return the same 3:30 result; this is because the previous code rounded the result to the nearest 5 minutes.
If you need the minutes, try this code

Function WorkedH2(ByRef InOut As Range, ByRef OfficeH As Range) As Date
Dim whArr, I As Double, Quant As Double
Dim MinTot As Long, cWDay As Long
'
Quant = 0.0001
whArr = OfficeH.Value
For I = CDbl(InOut.Cells(1, 1)) + 1 / 1440 To Int(InOut.Cells(1, 2)) + 20000 / 1440 Step 1 / 1440
    cWDay = Weekday(Int(I), 2)
    If (I - Int(I)) >= CDbl(whArr(cWDay, 2) + Quant) And (I - Int(I)) < CDbl(whArr(cWDay, 3) + Quant) Then
        MinTot = MinTot + 1
    End If
    If I > CDbl(InOut.Cells(1, 2)) - Quant Then Exit For
Next I
WorkedH2 = MinTot / 1440
End Function

Use WorkedH2 for this new version, or WorkedH for rounding to 5 minutes

 

[Excelexcel86]

No I mean the start time on the first row is 07-30 until 13.35 so the time in the shop should be 6.05 hours not 3:30

 

[Anthony47]

No I mean the start time on the first row is 07-30 until 13.35 so the time in the shop should be 6.05 hours not 3:30

But Feb 10 2023 is a friday, what you say now contradicts what you said in post #1

 

[Excelexcel86]

Hi

But Feb 10 2023 is a friday, what you say now contradicts what you said in post #1

sorry yes u are correct thank you very much just quick question the column that has numbers 1 to 7 does this stay the same if you want to calculate the days in office all year round ?

 

[Anthony47]

the column that has numbers 1 to 7 does this stay the same if you want to calculate the days in office all year round ?

Don't understand the question: 1-7 stays for Mon-Sun, and describe which are the working hours on each of the week days. Could you be more specific?

 

[Excelexcel86]

Ok sorry what I mean was if it goes to the next week does this still calculate so would it calculate 19/02/2023 07:30 - 19/02/2023 12.30 and so on for every week ?