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

 

[Anthony47]

Copy the code from my message and paste it to your vba module; don't retype it
That instruction is CDBL, not CDB1

 

[Excelexcel86]

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


Ok but Where does that formula go

 

[Anthony47]

From youy attachments it is impossible to detect which range you use; look at the image I attached to my first message.

So, supposing that Time entered warehouse is column A, time exited warehouse is column B, and data starts on row 2, and the weekly workinh hours are in Sheet2!A1:C7, and you need the result in C2 then in C2 you will insert

=WorkingH(A2:B2, Sheets2!$A$1:$C$7)

 

[James006]

It is not cdb1 BUT CDBL ...

The letter L and not the Number 1 ... why ...
Because, it stands for Convert DouBLe

 

[Excelexcel86]

From youy attachments it is impossible to detect which range you use; look at the image I attached to my first message.

So, supposing that Time entered warehouse is column A, time exited warehouse is column B, and data starts on row 2, and the weekly workinh hours are in Sheet2!A1:C7, and you need the result in C2 then in C2 you will insert

=WorkingH(A2:B2, Sheets2!$A$1:$C$7)

Ok I copied the formula but it’s giving me 1.05 hours time difference for time between 09.55 -1245

 

[Excelexcel86]

I really appreciate your help

 

[James006]

You are welcome

 

[Anthony47]

Ok I copied the formula but it’s giving me 1.05 hours time difference for time between 09.55 -1245

Could you show your data, at least a picture?

Here is what I get:

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Time entered warehouse	time exited warehouse	time inside warehouse for work hours	Total time								1	06:00	14:30
2	01/03/2023 07:30	01/03/2023 13:30	Should be 6 hours as it’s inside work hours		06:00							2	06:00	14:30
3	01/03/2023 09:00	02/03/2023 08:00	Should be be 7:30 hours	Should be 23 hours	07:30							3	06:00	14:30
4	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	03:00							4	06:00	14:30
5												5	06:00	11:00
6												6	00:00	00:00
7												7	00:00	00:00
8
9
10
Foglio5

Cell Formulas
Range	Formula
E2:E4	E2	=WorkedH(A2:B2,$L$1:$N$7)

 

[Excelexcel86]

Date and time entered	date and time completed	total time		1	06:30	14:30
10/02/2023 07:30	10/02/2023 12:55	1.05hrs		2

1 06:30 and 14:30 ante in cells LMN

Code is =workedH(a2:b2,$L$1:$n$7)

I’ve done the columns like you have for L M N

 

[Anthony47]

Share your data, this is my test bed and I get 3:30

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Time entered warehouse	time exited warehouse	time inside warehouse for work hours	Total time								1	06:00	14:30
2	01/03/2023 07:30	01/03/2023 13:30	Should be 6 hours as it’s inside work hours		06:00							2	06:00	14:30
3	01/03/2023 09:00	02/03/2023 08:00	Should be be 7:30 hours	Should be 23 hours	07:30							3	06:00	14:30
4	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	03:00							4	06:00	14:30
5	10/02/2023 07:30	10/02/2023 12:55			03:30							5	06:00	11:00
6												6	00:00	00:00
7												7	00:00	00:00
8
Foglio5

Cell Formulas
Range	Formula
E2:E5	E2	=WorkedH(A2:B2,$L$1:$N$7)