In his last desperate act before taking a handful of Ambien, M. DeRevere asks:

*How can I program a cell to accept time formulas without putting the (:) colons between the numbers… if you could format the cell to accept just number such as 2300 and it formats it as 23:00 it would save me sooooo much time?… HELP*

Well hold off on the pills M. Because I’m rockin the Excel skillz , and I happen to know the answer.

M’s question revolves around “military” time (the 24 hour clock). For you civilians out there, 23:00 hours is 11:00 pm in military time. The trick is to apply some custom number formatting.

**1. Highlight the column into which you’ll be entering your data. **

**2. Bring up the Format Cells dialog box and select the Number tab.**

**3. Choose Custom from the Category list.**

**4. In the Type field, enter 00\:00**

** **

At this point, any number you enter will automatically have a colon inserted into the number (which I suppose is better than having a number inserted into the colon ).

There are other ways to convert numbers to military time using formulas and such. This is probably the easiest way.

I would note that this is not truly a time format so there is nothing stopping anyone from putting something silly in a cell like 3345. But for relieving some of the burden of data entry, this techinque serves the purpose.

Keep in mind that performing mathematical operations on these pseudo-time values is not at all reliable.

11:30 – 11:15 gives you 00:15, which is correct.

13:10 – 11:15 gives you 1:95, which doesn’t make sense as a time.

It’s likely that many a military battle has been lost because of these incorrect time calculations. At the very least, some Excel-using soldiers have showed up late for mess.

You can always convert these pseudo times to real times using a formula like this:

=TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2))

Then format the formula cell as a time.

John: Please don’t say mess after my colon joke.

In any case, your warning rings true. If these ‘times’ are to be used in calculations, then a helper column with your suggested TIMEVALUE formula should be used.

Thanks J-walk.

Add data validation to the cell(s) to restrict entry to whole numbers between 0000 and 2359.

CarlS: Great idea.

I simply use “hhmm” in the custom syntax format and I have the same result without the problem for calculation (i.e. 13:10 – 11:15 gives you 1:55).

Sebastien: Using hhmm gives displays 0000 for anything I enter.

CarlS: The data validation is a great idea, but it will accept mistakes like “1089”, since it wouldn’t know it is a time format..

Datapig, that’s odd, maybe it’s due to different international settings in Windows. I’m set as English – Canada.

You can try adding a location settings to the custom format like this for “English – Canada”

[$-1009]hhmm

I also sent you my workbook by e-mail.

If you want to learn more on international number formats, you can go the the followin site

http://office.microsoft.com/en-ca/excel/HA010346351033.aspx

That is odd. Thanks for the link. I’ll take a look.

I think I might know why. Do you have integer in the cell or fractions?

I realize I’m late to this thread, but here is a VB solution I posted to the newsgroups a couple of years ago or so. It will allow you to enter the time in many different ways. For example, you can enter 3p, 3:00, 934, 1723, 1234p, and so on; in other words, if it is missing the colon, the colon will be inserted and then, if the entry can be made into a date at all, it will be, otherwise an error message will be issued. The following is event code and should be installed by right clicking the name tab at the bottom of the worksheet that is to have this functionality, select View Code from the popup menu that appears and then copy/paste this code into the code window that opened up…

Private Sub Worksheet_Change(ByVal Target As Range)

Dim T As String

Const TimeRange As String = “D:G”

If Target.Count > 1 Then Exit Sub

With Target

If Not Intersect(Target, Range(TimeRange)) Is Nothing Then

On Error GoTo CleanUp

Application.EnableEvents = False

T = .Value

If T Like “*[aApP]” Then

T = Replace(T, “a”, ” AM”, , , vbTextCompare)

T = Replace(T, “p”, ” PM”, , , vbTextCompare)

ElseIf T Like “*[AaPp][mM]” Then

T = Left(T, Len(T) – 2) & ” ” & Right(T, 2)

End If

If Not IsDate(T) And InStr(T, “:”) = 0 And Len(T) > 1 Then

T = Left(T, InStr(T & ” “, ” “) – 3) & “:” & _

Mid(T, InStr(T & ” “, ” “) – 2)

End If

T = WorksheetFunction.Trim(T)

If IsDate(T) Then

.Value = CDate(T)

Else

MsgBox “That is not a real time value!”

End If

End If

End With

CleanUp:

Application.EnableEvents = True

End Sub

All you need to do is set the TimeRange constant in the Const statement to the range you want to cover. In my example code above, I set that value to “D:G” which means columns D through G are set to convert entries into time values; but you can use any valid range address instead (for example, “C4:F9”) in order to restrict the code’s functionality to just the cells in that restricted range.

I received a compile error, when i placed this code in excel. it was on the Const TimeRange As String = “D:G” . It stated Constant expression required and had D highlighted. Any suggestions?

@Jimmy,

Try replacing all the quote marks… all of them, not just the ones on that line of code… this blog comment processor changes “normal” quote marks to opening/closing ones (notice that they are slanted instead of being straight up and down when you copy/paste the code). I think the code will run for you after you make that replacement.

I figured it out after 30 hours or more. The problem with 00:00 hours is that excel sees this number as 1 digit… “0”. Therefore, you have to set up separate columns that break down the cell containing the number into hours and minutes. First, you have to have separate columns to determine how many digits are in the cell. Example, the column headings will read “len=0”, “len=1”, “len=2″,”len=3″,”len=4”. If len =0, then you have to develop the formula in accordance to which time is subtracting from which. If len is =3, then you have hours and minutes. Then you have to begin your formulas. I think it took about 10 columns to get it to work. You must account for the zeros… 1300. There are no minutes here, so you must use “if(right(a1,len(a1)-2)=0,?)” where the ? would translate to “then minutes =0”. Etc.

That is nice. How do I get two colums in this format to add corectly (ie: 01:30 + 00:50 = 01:20)?

I am searching the web for a similar formula to use in an excel spreadsheet that requires military time also. The formula I found on this thread works however when I use it and enter a time then look at the formula bar, there isn’t a PM or AM after the time, which is required for an unknown reason by my supervisor. This is the best way I know to provide an example:

Without the formula, having to manually enter the colon:

13:00 entered into the cell will result in the formula box showing: 13:00:00 PM.

With the formula, not having to enter the colon (which is what I need):

13:00 entered into the cell will result in the formula box showing: 1300

My supervisor runs something at the end of the year using the statistics I’ve entered into this spreadsheet. He swears that “PM” or “AM” not showing in the formula box will affect his numbers.

Any help would be greatly appreciated! Thank you and have a great day!!

****Correction****

My example above is incorrect

13:00 entered into the cell will result in the formula box showing: 01:42:00 PM

not 13:00:00 PM

I am using Microsoft excel spreadsheet but when i put a formulas in excel sheet then excel sheet give me a massage

Massage :- you can’t enter formulas largest of 64.

But i want enter in the excel sheet approx 1100 formulas like

=IF(B3=6677,”ACIVIR IV”,IF(B3=6685,”ADVENT 1.2G INJ”,))

this is only to and i want enter in the excel sheet approx 1100 like that.

So kindly tell me how i could put the formulas in the excel sheet.

if possible so kindly give me revert as soon as possible.

Nice thread about military time, it got me thinking.

I need to keep it SIMPLE for my data entry people. I wanted to have a formula calculate the hours in an event. We use military time. I want the course coordinator to enter a date in cell A2, 800 in cell B2 (start time) and 1215 in cell C2 (end time) , event name in D2 etc.

Took me a while to realize I needed to work with the MOD (remainder) and ROUNDDOWN functions to calculate the difference in two numbers which represented military times.

Here is the formula I came up with for figuring out the difference between the end time and start time.

=(MOD(+C2,100)/60+ROUNDDOWN(C2/100,0))-(MOD(+B2,100)/60+ROUNDDOWN(B2/100,0))

gave me 4.25 hours.

I changed times to 835 and 1215 and the formula gave me 3.6667.

I settled for the custom format of 0000, which forced the leading zero when missing. I could limit the range for the column inputs.

Overview: I am trying to create an event calendar for a residential course, or at least an event planner. There are different size blocks of instruction and other events during the 16 week term. There are many instructors. They need to track different type of hours etc. It has always (25 years plus) been a manual system using Word. I decided to start with Excel and worry about making it pretty later 🙂 If they screw up the formula, at least they can manually put the hours in.

Hope this helps someone else.

so how to calculate hours if use this 00\:00.