Help with time formula and calculating time

[RandyD123]

I need a little help with this sheet. My time start(E) and time end(F) column can be any time format. I'm just using what you see for a starting point. I need to have column G calculate the total hours based on my start and end time. I would also like column H and I to populate based on who made the entry and when they did it. If it's easier, both of those columns could be combined. This file will be on a work network (Sharepoint). Any help would be much appreciated. I hope I posted this correctly. Thank You

Book1.xlsm
	B	C	D	E	F	G	H	I
2
3	STW (Person Working)	STO (Person Not Working)	Date Shift Trade Will Occur	Time Start	Time End	Total Hours Traded Off	Person Who Made This Entry	Date Person Made This Entry
4	Name #08	Name #03	01/17/23	0400	0800	4.0
5	Name #03	Name #01	02/27/23	1200	1630	4.5
6	Name #04	Name #03	03/15/23	1500	1830	3.5
7	Name #06	Name #01	04/22/23	0430	0730	3.0
8	Name #02	Name #04	04/28/23	0730	2000	12.5
9	Name #08	Name #04	05/02/23	1130	1730	6.0
10	Name #05	Name #02	05/14/23	0330	1200	8.5
11	Name #01	Name #03	06/12/23	1800	2000	2.0
12	Name #07	Name #01	07/04/23	1700	2400	7.0
13	Name #06	Name #04	08/19/23	0330	1200	8.5
14	Name #71	Name #02	07/30/23	0330	0730	4.0
15	Name #25	Name #02	12/14/23	1300	1800	5.0
16	Name #22	Name #01	09/22/23	0800	1200	4.0
17	Name #08	Name #02	08/02/23	0330	1200	8.5
18
19
20
21
22
Shift Trades

Cells with Data Validation
Cell	Allow	Criteria
B4:C22	List	=Data!$B$4:$B$104

 

[Dave Patton]

Time.xlsm
	A	B	C	D	E	F	G
1
2
3		STW (Person Working)	STO (Person Not Working)	Date Shift Trade Will Occur	Time Start	Time End	Total Hours Traded Off
4		Name #08	Name #03	44943	400	800	4.00
5		Name #03	Name #01	44984	1200	1630	4.50
6		Name #04	Name #03	45000	1500	1830	3.50
7		Name #06	Name #01	45038	430	730	3.00
8		Name #02	Name #04	45044	730	2000	12.50
9		Name #08	Name #04	45048	1130	1730	6.00
10		Name #05	Name #02	45060	330	1200	8.50
11		Name #01	Name #03	45089	1800	2000	2.00
12		Name #07	Name #01	45111	1700	2400	7.00
13		Name #06	Name #04	45157	330	1200	8.50
14		Name #71	Name #02	45137	330	730	4.00
15		Name #25	Name #02	45274	1300	1800	5.00
16		Name #22	Name #01	45191	800	1200	4.00
17		Name #08	Name #02	45140	330	1200	8.50
1aa

Cell Formulas
Range	Formula
G4:G17	G4	=(--TEXT(LEFT(F4,4),"00\:00")-TEXT(LEFT(E4,4),"00\:00"))*24

 

[Dave Patton]

Time.xlsm
	A	B	C	D	E	F	G
1
2
3		STW (Person Working)	STO (Person Not Working)	Date Shift Trade Will Occur	Time Start	Time End	Total Hours Traded Off
4		Name #08	Name #03	44943	400	800	4.00
5		Name #03	Name #01	44984	1200	1630	4.50
6		Name #04	Name #03	45000	1500	1830	3.50
7		Name #06	Name #01	45038	430	730	3.00
8		Name #02	Name #04	45044	730	2000	12.50
9		Name #08	Name #04	45048	1130	1730	6.00
10		Name #05	Name #02	45060	330	1200	8.50
11		Name #01	Name #03	45089	1800	2000	2.00
12		Name #07	Name #01	45111	1700	2400	7.00
13		Name #06	Name #04	45157	330	1200	8.50
14		Name #71	Name #02	45137	330	730	4.00
15		Name #25	Name #02	45274	1300	1800	5.00
16		Name #22	Name #01	45191	800	1200	4.00
17		Name #08	Name #02	45140	330	1200	8.50
18
1aa

Cell Formulas
Range	Formula
G4:G17	G4	=(TEXT(LEFT(F4,4),"00\:00")-TEXT(LEFT(E4,4),"00\:00"))*24

 

[Dave Patton]

Time.xlsm
	A	B	C	D	E	F	G
1
2
3		STW (Person Working)	STO (Person Not Working)	Date Shift Trade Will Occur	Time Start	Time End	Total Hours Traded Off
4		Name #08	Name #03	44943	400	800	4.00
5		Name #03	Name #01	44984	1200	1630	4.50
6		Name #04	Name #03	45000	1500	1830	3.50
7		Name #06	Name #01	45038	430	730	3.00
8		Name #02	Name #04	45044	730	2000	12.50
9		Name #08	Name #04	45048	1130	1730	6.00
10		Name #05	Name #02	45060	330	1200	8.50
11		Name #01	Name #03	45089	1800	2000	2.00
12		Name #07	Name #01	45111	1700	2400	7.00
13		Name #06	Name #04	45157	330	1200	8.50
14		Name #71	Name #02	45137	330	730	4.00
15		Name #25	Name #02	45274	1300	1800	5.00
16		Name #22	Name #01	45191	800	1200	4.00
17		Name #08	Name #02	45140	330	1200	8.50
1aa

Cell Formulas
Range	Formula
G4:G17	G4	=(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24

 

[RandyD123]

Which one do I use?

 

[Dave Patton]

Try =(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24

Sorry for the numerous posts. I thought that I was editing my first post but new posts were created.

Which suggestion(s) did you try.

You could enter the time as time not as text. 16:00 or with the Time function.

 

[RandyD123]

Try =(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24

Sorry for the numerous posts. I thought that I was editing my first post but new posts were created.

Which suggestion(s) did you try.

You could enter the time as time not as text. 16:00 or with the Time function.

Yes I did that one and it seems to work perfectly. Thank you. Any insight on the other 2 columns?

 

[Dave Patton]

Any insight on the other 2 columns?
I would also like column H and I to populate based on who made the entry and when they did it.

Sorry No
You could search for automatic stamp of username and date/time.

 

[smozgur]

Yes I did that one and it seems to work perfectly. Thank you.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.