Entering time without using colon

[acvaus]

Hi, I am new this all, you help would be very much appreciated.

I am using Excel, and seeking some help in formulating a formula that allows me to key in time quicker and easier than the continual shift colon method of entering the hours and minute into a cell.

I am trying to put together a a weekly shift rota that calculates the number of hours each employee has been pencilled in to work. As I have alot of people to rota, I am looking for a way to enter the shift start and finish times (using 24 hour clock) into the rota without having to use the semi colon ":" prefering to enter the time using milatary time "0700" or a dot "23.00" but a formula displays the time correctly as "12:00 AM" etc. Using the correct time format then allows for the time to be recognised and the hours worked to be calculated.

Thanks in advance

[shg]

See http://www.pcmag.com/article2/0,2817,2316755,00.asp

[djapigo]

Using military time like 0730 or 1530, you can use the following formula:

=TIME(INT(A1/100),A1-INT(A1/100)*100,0)

Where A1 is where you put your military time.

One other suggestion is to change your number format to 0000 so that it will always show the leading 0.

Let me know if you need clarification.

Dennis

[shg]

Or =--TEXT(A1, "00\:00")