Timesheet - Time data issue

[Jods70]

Hi guys.

First post but I've spent the last 6 hours trying to figure this one out. I work for a transport company & our drivers currently fill in their timesheets on printed paper, then take a photo of it & email it to me so I can process their pay. To say this is time-consuming is an understatement as I need to enter their times manually to then work out the number of hours over 80 hours as this needs to be entered seperately.

I'm trying to make life easier for them & myself by having the spreadsheet calculate all of this as the data is entered, my problem is they will be entering the data on either a tablet or mobile phone & having to enter the : "colon" for excel to register that they are times won't really work with my guys.

I'm currently using custom cell formatting of hhmm but it's not always working as the cells change to date/time format & throughs all the calculations out per my image.

Any & all help will be most appreciated. Thanks Jods

 

[Dave Patton]

Adjust the columns as necessary.
You can paste this post into a clean sheet and review the formulas. Select the cell below the f(x) in the heading and then move to your sheet and paste.
Probably K5 is the relevant formula. K3 and K4 are just initial examples of the concept.

Conversion.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Day	Date	Start	Lunch	Lunch	Dinner	Dinner	Finish				Base
3	Mon	Dec-12	0615	0930			1200	2000			11.25	8	8.00	26,993.25	27,001.25
4			0800	0930	1000	1200	1300	1830			9.00	8	8.00
5			0800	0930	1000	1200	1300	1830			9.00	8	8.00
2e

Cell Formulas
Range	Formula
O3	O3	=N3+M3
K3	K3	=(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"))*24+(TEXT(H3,"00\:00")-TEXT(G3,"00\:00"))*24
K4	K4	=(TEXT(D4,"00\:00")-TEXT(C4,"00\:00"))*24+(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24+(TEXT(H4,"00\:00")-TEXT(G4,"00\:00"))*24
K5	K5	=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24
M3:M5	M3	=MIN(L3,K3)

 

[Jods70]

Adjust the columns as necessary.
You can paste this post into a clean sheet and review the formulas. Select the cell below the f(x) in the heading and then move to your sheet and paste.
Probably K5 is the relevant formula. K3 and K4 are just initial examples of the concept.

Conversion.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Day	Date	Start	Lunch	Lunch	Dinner	Dinner	Finish				Base
3	Mon	Dec-12	0615	0930			1200	2000			11.25	8	8.00	26,993.25	27,001.25
4			0800	0930	1000	1200	1300	1830			9.00	8	8.00
5			0800	0930	1000	1200	1300	1830			9.00	8	8.00
2e

Cell Formulas
Range	Formula
O3	O3	=N3+M3
K3	K3	=(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"))*24+(TEXT(H3,"00\:00")-TEXT(G3,"00\:00"))*24
K4	K4	=(TEXT(D4,"00\:00")-TEXT(C4,"00\:00"))*24+(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24+(TEXT(H4,"00\:00")-TEXT(G4,"00\:00"))*24
K5	K5	=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24
M3:M5	M3	=MIN(L3,K3)

Hi Dave.

Thank you for this but it isn't working for me I've also been informed today that the drivers require a third break period. Here's the first few rows of what I'm trying to fix, that might make it easier to figure out what I've done wrong. Thanks again for the assistance

Fortnightly Time Sheets - MTAHRFORM3.0.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Day	Date	Start	Lunch	Lunch	Dinner	Dinner	Supper	Supper	Finish	Base 8	Over 8	Total	Job Description: Ref #'s Con-Notes
4			Time	Start	Finish	Start	Finish	Start	Finish	Time	Hrs/Day	Hrs/Day	Hrs
5	Mon	12-Dec	800	900							0000	2392	2393
6	Tue	13-Dec									0	0	0
7	Wed	14-Dec									0	0	0
8	Thu	15-Dec									0	0	0
Timesheet

Cell Formulas
Range	Formula
K5	K5	=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24
L5:L8	L5	=IF(((D5-C5)+(F5-E5)+(H5-G5))*24>8,((D5-C5)+(F5-E5)+(H5-G5))*24-8,0)
M5:M8	M5	=L5+K5
K6:K8	K6	=(MOD(G6-B6,1)-MOD(D6-C6,1)-MOD(F6-E6,1))*24
B5	B5	=N2-13
B6	B6	=N2-12
B7	B7	=N2-11
B8	B8	=N2-10

 

[Dave Patton]

Conversion.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								info
2
3		Date	Start	Lunch	Lunch	Dinner	Dinner	Supper	Supper	Finish	Total	Base 8	Over 8	Total
4			Time	Start	Finish	Start	Finish	Start	Finish	Time	Hours	Hrs/Day	Hrs/Day	Hrs
5		Mon 12-Dec-22	800	900							1	1	0	1
6		Tue 13-Dec-22	800	900	930	1250	1320	1800	1830	2000	9	8	1	9
2ee

Cell Formulas
Range	Formula
K5:K6	K5	=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24
L5:L6	L5	=MIN(K5,8)
M5:M6	M5	=IF(K5>8,K5-8,0)
N5:N6	N5	=L5+M5

 

[Jods70]

Conversion.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								info
2
3		Date	Start	Lunch	Lunch	Dinner	Dinner	Supper	Supper	Finish	Total	Base 8	Over 8	Total
4			Time	Start	Finish	Start	Finish	Start	Finish	Time	Hours	Hrs/Day	Hrs/Day	Hrs
5		Mon 12-Dec-22	800	900							1	1	0	1
6		Tue 13-Dec-22	800	900	930	1250	1320	1800	1830	2000	9	8	1	9
2ee

Cell Formulas
Range	Formula
K5:K6	K5	=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24
L5:L6	L5	=MIN(K5,8)
M5:M6	M5	=IF(K5>8,K5-8,0)
N5:N6	N5	=L5+M5

Thank you SO MUCH Dave that worked a treat. Have a wonderful Xmas & New Year

 

[Dave Patton]

Thanks for the feedback and Merry Christmas to you.