Macro

Excelexcel86

Board Regular
Joined
Feb 28, 2023
Messages
99
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:3001/03/2023 13.30Should be 6 hours as it’s inside work hours
01/03/2023 09.0002/03/2023 08:00Should 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:0010/03/2023 12:00This is a Friday so should be 3hrs as finish at 11:00Should be 4 hours
 
Copy the code from my message and paste it to your vba module; don't retype it
That instruction is CDBL, not CDB1
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
WorkingH(Start/EndDate&Time, DailyWorkingHours) as Date


Ok but Where does that formula go
 
Upvote 0
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
Excel Formula:
=WorkingH(A2:B2, Sheets2!$A$1:$C$7)
 
Upvote 0
It is not cdb1 BUT CDBL ...

The letter L and not the Number 1 ... why ...
Because, it stands for Convert DouBLe
 
Upvote 0
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
Excel Formula:
=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
 
Upvote 0
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
ABCDEFGHIJKLMN
1Time entered warehousetime exited warehousetime inside warehouse for work hoursTotal time106:0014:30
201/03/2023 07:3001/03/2023 13:30Should be 6 hours as it’s inside work hours06:00206:0014:30
301/03/2023 09:0002/03/2023 08:00Should be be 7:30 hours Should be 23 hours07:30306:0014:30
410/03/2023 08:0010/03/2023 12:00This is a Friday so should be 3hrs as finish at 11:00Should be 4 hours03:00406:0014:30
5506:0011:00
6600:0000:00
7700:0000:00
8
9
10
Foglio5
Cell Formulas
RangeFormula
E2:E4E2=WorkedH(A2:B2,$L$1:$N$7)
 
Upvote 0
Date and time entered date and time completed total time106:3014:30
10/02/2023 07:3010/02/2023 12:551.05hrs2

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
 
Upvote 0
Share your data, this is my test bed and I get 3:30

Cartel1
ABCDEFGHIJKLMNO
1Time entered warehousetime exited warehousetime inside warehouse for work hoursTotal time106:0014:30
201/03/2023 07:3001/03/2023 13:30Should be 6 hours as it’s inside work hours06:00206:0014:30
301/03/2023 09:0002/03/2023 08:00Should be be 7:30 hours Should be 23 hours07:30306:0014:30
410/03/2023 08:0010/03/2023 12:00This is a Friday so should be 3hrs as finish at 11:00Should be 4 hours03:00406:0014:30
510/02/2023 07:3010/02/2023 12:5503:30506:0011:00
6600:0000:00
7700:0000:00
8
Foglio5
Cell Formulas
RangeFormula
E2:E5E2=WorkedH(A2:B2,$L$1:$N$7)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top