Formula to input times from another sheet

[Excelexcel86]

the first sheet is ware house times and dates teh

second sheet is where i want to calculate the times.

DATE	DAY	STARTTIME	ENDTIME	DAYTIME HOURS	DAYTIME MINUTES
06/02/2023​	Mon​	6:30​	14:30​	8:00​	480.00​
07/02/2023​	Tue​	6:30​	14:30​	8:00​	480.00​
08/02/2023​	Wed​	6:30​	14:30​	8:00​	480.00​
09/02/2023​	Thu​	6:30​	14:30​	8:00​	480.00​
10/02/2023​	Fri​	6:00​	11:00​	5:00​	300.00​
11/02/2023​	Sat​	0:00​	24:00​	24:00​	1440.00​
12/02/2023​	Sun​	0:00​	24:00​	24:00​	1440.00​
13/02/2023​	Mon​	6:30​	14:30​	8:00​	480.00​
14/02/2023​	Tue​	6:30​	14:30​	8:00​	480.00​
15/02/2023​	Wed​	6:30​	14:30​	8:00​	480.00​
16/02/2023​	Thu​	6:30​	14:30​	8:00​	480.00​
17/02/2023​	Fri​	6:00​	11:00​	5:00​	300.00​
18/02/2023​	Sat​	0:00​	24:00​	24:00​	1440.00​
19/02/2023​	Sun​	0:00​	24:00​	24:00​	1440.00​
20/02/2023​	Mon​	6:30​	14:30​	8:00​	480.00​

Date and Time entered warehouse	Date and time exited warehouse	time was in warehouse work hours only	total time
06/02/2023 10:20	07/02/2023 06:20
06/02/2023 11:00	07/02/2023 06:40
06/02/2023 12:25	07/02/2023 06:10
07/02/2023 09:25	07/02/2023 10:20
07/02/2023 09:35	07/02/2023 14:40	4.55​	5.05​
07/02/2023 10:55	08/02/2023 02:15
07/02/2023 11:00	08/02/2023 06:15	03:30​	19:40​
07/02/2023 12:55	07/02/2023 19:50
07/02/2023 14:00	08/02/2023 06:25
08/02/2023 09:40	08/02/2023 12:00
08/02/2023 09:50	08/02/2023 15:20
08/02/2023 10:15	09/02/2023 04:00
08/02/2023 10:30	09/02/2023 00:15
08/02/2023 10:45	08/02/2023 19:40
08/02/2023 11:40	08/02/2023 18:15
08/02/2023 11:45	08/02/2023 16:45
09/02/2023 07:30	09/02/2023 10:00
09/02/2023 10:20	09/02/2023 12:50
09/02/2023 11:00	09/02/2023 20:30
09/02/2023 13:20	09/02/2023 18:10

 

[Excelexcel86]

any look anybody with any help at all really at a loss

 

[awoohaw]

What are the 'manual' calculations you used in the bottom worksheet to get the time in warehouse and total time calculations. ?

 

[Excelexcel86]

Them I just typed in as 11.00 in the morning is in work time and it was still there past 14.30 so I just wrote it in as it should be 3.30 it also didn’t go into the next day start time so no more hours was needed to be added .I’m struggling with a formula to calculate all of this without having to manually work this out every time

 

[awoohaw]

i just need a basic formula. End Time minus Start Time...with adjustments for lunch break (how long is lunch break, and is there an amount of hours that must happen before a lunchbreak can be subtracted. Will a start to end time ever be more than 24 hours?

Them I just typed in as 11.00 in the morning is in work time and it was still there past 14.30 so I just wrote it in as it should be 3.30 it also didn’t go into the next day start time so no more hours was needed to be added .I’m struggling with a formula to calculate all of this without having to manually work this out every time

 

[Excelexcel86]

There is no break times to be included and the start times and end times are always going to be the same for every week

 

[awoohaw]

okay. not sure if this is what you want or not:
'

mr excel questions 12.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	DAY	START TIME	END TIME	DAYTIME HOURS	DAYTIME MINUTES		Hours	Minutes
2	2023-02-06	Mon	06:30	14:30	08:00	480		8.0000000	480.0000000
3	2023-02-07	Tue	06:30	14:30	08:00	480		8.0000000	480.0000000
4	2023-02-08	Wed	06:30	14:30	08:00	480		8.0000000	480.0000000
5	2023-02-09	Thu	06:30	14:30	08:00	480		8.0000000	480.0000000
6	2023-02-10	Fri	06:00	11:00	05:00	300		5.0000000	300.0000000
7	2023-02-11	Sat	00:00	00:00	00:00	0		0.0000000	0.0000000
8	2023-02-12	Sun	00:00	00:00	00:00	0		0.0000000	0.0000000
9	2023-02-13	Mon	06:30	14:30	08:00	480		8.0000000	480.0000000
10	2023-02-14	Tue	06:30	14:30	08:00	480		8.0000000	480.0000000
11	2023-02-15	Wed	06:30	14:30	08:00	480		8.0000000	480.0000000
12	2023-02-16	Thu	06:30	14:30	08:00	480		8.0000000	480.0000000
13	2023-02-17	Fri	06:00	11:00	05:00	300		5.0000000	300.0000000
14	2023-02-18	Sat	00:00	00:00	00:00	0		0.0000000	0.0000000
15	2023-02-19	Sun	00:00	00:00	00:00	0		0.0000000	0.0000000
16	2023-02-20	Mon	06:30	14:30	08:00	480		8.0000000	480.0000000
17
18
19
20	Date and Time entered warehouse	Date and time exited warehouse	time was in warehouse work hours only	Time as decimal Hours	Time as decimal minutes
21
22	2023-02-06 06:30	2023-02-06 14:30	08:00:00	8.00000000	480
23	2023-02-07 06:30	2023-02-07 14:30	08:00:00	8.00000000	480
24	2023-02-08 06:30	2023-02-08 14:30	08:00:00	8.00000000	480
25	2023-02-09 06:30	2023-02-09 14:30	08:00:00	8.00000000	480
26	2023-02-10 06:00	2023-02-10 11:00	05:00:00	5.00000000	300
27	2023-02-11 00:00	2023-02-11 00:00	00:00:00	0.00000000	0
28	2023-02-12 00:00	2023-02-12 00:00	00:00:00	0.00000000	0
29	2023-02-13 06:30	2023-02-13 14:30	08:00:00	8.00000000	480
30	2023-02-14 06:30	2023-02-14 14:30	08:00:00	8.00000000	480
31	2023-02-15 06:30	2023-02-15 14:30	08:00:00	8.00000000	480
32	2023-02-16 06:30	2023-02-16 14:30	08:00:00	8.00000000	480
33	2023-02-17 06:00	2023-02-17 11:00	05:00:00	5.00000000	300
34	2023-02-18 00:00	2023-02-18 00:00	00:00:00	0.00000000	0
35	2023-02-19 00:00	2023-02-19 00:00	00:00:00	0.00000000	0
36	2023-02-20 06:30	2023-02-20 14:30	08:00:00	8.00000000	480
37
Sheet18

Cell Formulas
Range	Formula
H2:H16	H2	=(IF(C2>D2,1,0)+D2-C2)*24
I2:I16	I2	=(IF(C2>D2,1,0)+D2-C2)*24*60
A22:A36	A22	=A2+C2
B22:B36	B22	=A2+IF(C2>D2,1,0)+D2
C22:C36	C22	=B22-A22
D22:D36	D22	=C22*24
E22:E36	E22	=C22*24*60

 

[Excelexcel86]

Hi the bottom chart time in work hours only is where I need the end hours to be from the hours times from the top sheet what formula is this ? Thanks for you help so far

 

[awoohaw]

Hi the bottom chart time in work hours only is where I need the end hours to be from the hours times from the top sheet what formula is this ? Thanks for you help so far

i'm sorry, I don't understand. Can you take what I have and put what values you want to see in another column? (and tell me how you want it caclulated... Cell(??) + Cell(??) etc.

 

[Excelexcel86]

Date and Time entered warehouse	Date and time exited warehouse	time was in warehouse work hours only
06/02/2023 10:20	06/02/2023 10:20	use a formula so the set hours that are in the top sheet calculates the hours that item entered warehouse and exited
06/02/2023 10:20	06/02/2023 10:20
08/02/2023 10:20 am	08/02/2023 14.50 pm	a formula should calculate this to 4hrs 10 minutes because the shift finished at 14.30 . so the formula goes in this cell to take all the info from the first sheet that then looks at the first 2 columns here and calculates when i say sheets they are on separate tabs one called ware house times and the other is the data which is this one