Timesheet: easy entry, then calculate

[Mahpie]

Hi lovely Excel forumers. I've searched, but I keep finding things that are quite different from what I want to do.

I am making a timesheet entry form.

I want people to be able to enter the time in 4 key presses (e.g. for 12:45, I just want them to be able to type 1245, and not need to mess about entering a colon in the middle).

To get that to work (for some value of "worked"), I changed the format of the cells to a custom number type: 00\:00

But, of course, when it goes to calculate 1200 - 0830 (to work out hours worked in the morning) it gets 03:70. Which is bad.

So data entry is fine - it looks great! But I'm guessing I need to convert those numbers back into times before I can do the "end time minus the start time = hours worked" calculations, right?

So assuming the users aren't dopes and actually enter valid times, is there a way I can convert times in the format of 00\:00 into actual hh:mm format?

 

[jorismoerings]

Hi and welcome to MrExcel,

With changing the number format to create an easy input you've changed the actual input from time to general number and hence the calculation of 12:00 -/- 08:30 will in fact be 1200-830.

You were on the right track with the remark to convert the numbers back into time.

try this:

Book1
	A	B	C	D	E	F	G
1	08:30	12:00		8:30:00	12:00:00	3:30:00	3,50
Sheet1

Cell Formulas
Range	Formula
D1	=TIME(LEFT(A1,IF(LEN(A1)=3,1,2)),RIGHT(A1,2),)
E1	=TIME(LEFT(B1,IF(LEN(B1)=3,1,2)),RIGHT(B1,2),)
F1	=E1-D1
G1	=(E1-D1)*24

Cells D1,E1`and F1 and cell with a time format [hh]:mm

Hope this helps.

 

[Mahpie]

Thank you so much for the very quick reply. I'll give that a go on Monday and report back!

 

[Mahpie]

Hi and welcome to MrExcel,

With changing the number format to create an easy input you've changed the actual input from time to general number and hence the calculation of 12:00 -/- 08:30 will in fact be 1200-830.

You were on the right track with the remark to convert the numbers back into time.

try this:

	A	B	C	D	E	F	G
1	08:30	12:00		8:30:00	12:00:00	3:30:00	3,50

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D1 =TIME(LEFT(A1,IF(LEN(A1)=3,1,2)),RIGHT(A1,2),) E1 =TIME(LEFT(B1,IF(LEN(B1)=3,1,2)),RIGHT(B1,2),) F1 =E1-D1 G1 =(E1-D1)*24 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Cells D1,E1`and F1 and cell with a time format [hh]:mm

Hope this helps.

It worked! Thanks heaps.

I did have to allow for errors, since blank entries gave me the dreaded #VALUE! all over the place, which messed up some of the calculations. So the code I used in the end was:

=IFERROR(TIME(LEFT(A1,IF(LEN(A1)=3,1,2)),RIGHT(A1,2),), 0)

...and formatting took care of the rest.

Thanks again!