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

 

[James006]

Hi,
Which formula are you currently using ?

Your macro will very probably follow the very same logic ...

 

[Excelexcel86]

=24IF(INT(B2)=INT(A2), MEDIAN(MOD(B2,1),TIME(6,0,0),TIME(14,30,0)) -MEDIAN(MOD(A2,1),TIME(6,0,0),TIME(14,30,0)), IF(INT(B2)-INT(A2)>=1, MEDIAN(MOD(B2,1),TIME(6,0,0),TIME(14,30,0))-TIME(6,0,0) +TIME(14,30,0)-MEDIAN(MOD(A2,1),TIME(6,0,0),TIME(14,30,0)) +(( INT(B2)-INT(A2)-1)*(TIME(14,30,0)-TIME(6,0,0))),""))



This is the formula but obviously it does take the Friday into account

 

[Excelexcel86]

Hi guys any ideas for me

 

[Anthony47]

My suggestion is that you go with a User Defined Function.
For example:
-insert somewhere in your file a table with the working hours for each of the weekdays (see the image, the orange area)
-copy the folllowing code into a standard module of your vba project:

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

Now you have available the Function WorkingH with the following syntax:

WorkingH(Start/EndDate&Time, DailyWorkingHours) as Date

In my test sheet (see the image) I used in I2:

=WorkedH(A2:B2,$L$1:$N$7)

Copy the formula down as necessary

 

[Excelexcel86]

My suggestion is that you go with a User Defined Function.
For example:
-insert somewhere in your file a table with the working hours for each of the weekdays (see the image, the orange area)
-copy the folllowing code into a standard module of your vba project:

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

Now you have available the Function WorkingH with the following syntax:

WorkingH(Start/EndDate&Time, DailyWorkingHours) as Date

In my test sheet (see the image) I used in I2:

=WorkedH(A2:B2,$L$1:$N$7)

Copy the formula down as necessary

Hi thanks for that how do I link it to my other sheet that you seen in my other question to pick up the work hours

 

[Anthony47]

My code require somewhere a table with the weekly working hours, like the one in orange in my previous image. You have something similar in the area outlined in orage in the attached image; but it needs the first column to be named 1 /2 /3 etc rather than Mon /Tue /Wed etc, and should also include lines 6=Sat and 7=Sun
The area in green is the Start/EndDate&time, first parametre for WorkedH

 

[Excelexcel86]

My code require somewhere a table with the weekly working hours, like the one in orange in my previous image. You have something similar in the area outlined in orage in the attached image; but it needs the first column to be named 1 /2 /3 etc rather than Mon /Tue /Wed etc, and should also include lines 6=Sat and 7=Sun
The area in green is the Start/EndDate&time, first parametre for WorkedH

Does it have to be in the same sheet tho or can I keep it in separate sheet as it is and link it to where my data is ?

 

[Anthony47]

It may be in a separate sheet, as with any excel function; for example

=WorkedH(A2:B2,Sheet123!$A$1:$C$7)

In this example the weekly table is in Sheet123 range A1:C7

 

[Excelexcel86]

What is cdb1 in the macro as it’s saying it is not defined