Is there a way to enter times without typing a colon?

  • Thread starter Thread starter dk_
  • Start date Start date
D

dk_

It there a way to enter times without 'typing' a colon in an empolyee
time sheet, and still be able to add the number of hours worked?

....For example, type 0930 and 2130 instead of having to type either 9:30
or 09:30 and 21:30, and still be able to do the math on figuring the
number of hours worked in a day?

Thanks.

-Dennis
 
Hi Dennis,

One way

A1 0930 (Start time)
A2 1430 (Start lunch)
A3 1500 (End Lunch)
A4 2130 (Finish time)
A5
=((CEILING(A4,25)-CEILING(A1,25))-((CEILING(A3,25)-CEILING(A2,25))))/100

This will round to the nearest quarter hour.
You will need a different approach if your shift
crosses midnight.

Format A1 to custom 0000

HTH
Martin
 
Hi Dennis,

http://xldynamic.com/source/xld.QDEDownload.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| It there a way to enter times without 'typing' a colon in an empolyee
| time sheet, and still be able to add the number of hours worked?
|
| ...For example, type 0930 and 2130 instead of having to type either 9:30
| or 09:30 and 21:30, and still be able to do the math on figuring the
| number of hours worked in a day?
|
| Thanks.
|
| -Dennis
|
| --
| Dennis Kessler
| http://www.denniskessler.com/acupuncture
 
David,

I copied your formula in B1.

I entered 0930 in A1

The result was 6:00 PM in B1

I don't see what this formula should do.

I was trying to find a way to enter 0930 or 930, with no colon to be
computed in a formula as 9:30 AM.

Thanks.

-Dennis
 
Martin,

I did not try your formula, because a time could possibly cross midnight.

What does that formula do?

Thanks.

-Dennis
 
Niek,

Thank you for the link to your very developed add-in.

I did download and install it. I started to read the help that you
included in the add-in, but there is a lot to the help.

Would you point me to what I have to do to be able to use a time entry
format... 0930 or 930 to be understood by Excel as 9:30 AM, etc.

Thanks.

-Dennis
 
Sorry, my error. That formula was one I'd given as answer to someone who
wanted to enter number in the format 09.30. For your format, it needs to be
=TIME(INT(A1/100),100*MOD(A1/100,1),0)
 
Back
Top