Timesheet Conversion Help

[digi_virus]

I recently started a new job, and one of my responsibilities is the weekly data entry for our timesheets from the punched time cards. They had a simple spreadsheet set up for the formulas for calculating the hours worked after the times we converted from standard(2:43pm) to military with a decimal for the minutes(14.72). The issue is that the conversion is done by hand, for the hours that isn't so much of an issue but the conversion for the minutes I was given a sheet to use. Is there a simple formula to enter the standard time of 2:43pm in that example on a different section of the spread sheet, and have the formula convert it into military with decimals in the correct chart position? Also, we run mulitple shifts so workers who start at say 4:54pm and punch out at 2:37pm would be notated as clocking in at 16.75 and out at 26.62 giving them a working time of 9.87 hours.


Thank you for any help.

 

[Micron]

The whole idea seems a bit overly complicated to me. If you're looking for a vba solution, try DateDiff function. This is a very common issue for people wanting to get hours and minutes between 2 dates where the date includes the time component. I imagine your cards have that.
Also figure that the 2:37 is AM of the next day rather than PM as you posted, in which case I get 9.716

If I'm wrong about the start and end times spanning over dates then ignore all that.

 

[Micron]

Again, if spanning days, consider

FormatDateTime(DateDiff("s",#04/04/2022 4:54:00 PM#,#04/05/2022 2:37:00 AM#)/86400,4)

I get 09:43, which is 9 hours and 43 minutes according to those date/time values.

 

[Snakehips]

@digi_virus If I understand correctly, you are manually converting punched card times to your decimal military times which you then enter into a spreadsheet.
Dealing with time that may or may not span midnight can be more awkward than you might think especially if you are not using Excel time inclusive of date .

Vba certainly could be of value if you are able / willing to go that route.
Alternatively, below is an illustration of what could be a formula approach.
Coumns J:K would be the extra columns ('on a different area of the spreadsheet')
Formatted as Text, this where you would enter your Card In and Out times, in the format 3:00am, 10:33am, 9:00pm etc

Formulas in B and C, hopefully, convert those text entries to your required decimal military times.
Those times are used to compute hours worked and pay or whatever.
I think the only issue would be if you had any In or Out time that was exactly midnight!?

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1		Converted	Converted							Card	Card
2	Employee Name	In	Out	Hrs	£/Hr	Pay				In	Out
3	Employee 1	16.90	26.62	9.72	10.00	$97.20				4:54pm	2:37am
4	Employee 2	16.90	22.62	5.72	12.00	$68.64				4:54pm	10:37pm
5	Employee 3	2.72	8.62	5.90	10.00	$59.00				2:43am	8:37am
6	Employee 4	23.75	29.00	5.25	12.00	$63.00				11:45pm	5:00am
7	Employee 5	4.00	27.00	23.00	12.00	$276.00				4:00am	3:00am
8	Employee 6	3.50	26.50	23.00	10.00	$230.00				3:30am	2:30am
9	Employee 7	8.00	17.00	9.00	12.00	$108.00				8:00am	5:00pm
Sheet1

Cell Formulas
Range	Formula
B3:B9	B3	=IFERROR(LET(In,TIMEVALUE(LEFT(J3,LEN(J3)-2))+IF(RIGHT(J3,2)="pm",0.5,0),HOUR(In)+ROUND(MINUTE(In)/60,2)),0)
C3:C9	C3	=IFERROR(LET(Cout,TIMEVALUE(LEFT(K3,LEN(K3)-2))+IF(RIGHT(K3,2)="pm",0.5,0),HOUR(Cout)+ROUND(MINUTE(Cout)/60,2)+IF(Cout<TIMEVALUE(LEFT(J3,LEN(J3)-2))+IF(RIGHT(J3,2)="pm",0.5,0),24,0)),0)
D3:D9	D3	=C3-B3
F3:F9	F3	=D3*E3

Hope that helps.

 

[Dave Patton]

A few ideas that you can consider. If you do not require all the detail, look at the last row.
Format the time to your preference.

Time.xlsm
	A	B	C	D	E	F	G	H
1		Card	Card	Converted	Converted
2	Employee Name	In	Out	In	Out	Hrs	£/Hr	Pay
3	Employee 1	4:54pm	2:37am	04:54 PM	02:37 AM	9.72	10.00	97.20
4	Employee 2	4:54pm	10:37pm	16:54	22:37	5.72	11.00	62.92
5	Employee 3	2:43am	8:37am	02:43	08:37	5.90	12.00	70.80
6	Employee 4	11:45pm	5:00am	23:45	05:00	5.25	13.00	68.25
7	Employee 5	4:00am	3:00am	04:00	03:00	23.00	14.00	322.00
8	Employee 6	3:30am	2:30am	03:30	02:30	23.00	15.00	345.00
9	Employee 7	8:00am	5:00pm	08:00	17:00	9.00	16.00	144.00
10
11	Employee 1	4:54pm	2:37am			9.72	10.00	97.20
7a

Cell Formulas
Range	Formula
D3:E9	D3	=--SUBSTITUTE(SUBSTITUTE(B3,"a"," a"),"p"," p")
F3:F9	F3	=ROUND((E3-D3+(D3>E3))*24,2)
H3:H9,H11	H3	=F3*G3
F11	F11	=LET(i,--SUBSTITUTE(SUBSTITUTE(B11,"a"," a"),"p"," p"),o,--SUBSTITUTE(SUBSTITUTE(C11,"a"," a"),"p"," p"),ROUND((o-i+(i>o))*24,2))

 

[Snakehips]

@Dave Patton I reckon that nails it. I'm wiser for it.

 

[Dave Patton]

Thanks Snakehips. Hopefully, the OP tries your suggestion and also my suggestion.

The Op could determine if the Time system can export in a format that doesn't require the conversions.