Free Microsoft Excel 2013 Quick Reference

Enter military time without colon


This tip refers to time-accounting:

Instead of forcing users to enter ":" for time like 10:00 and instead of using a VBA-solution following Custom time-formatting can be applied:


Just be aware that it will have the value of the number entered and not a true time value.

Post your answer or comment

comments powered by Disqus
Hi everyone

I have been working on a working hours calculator for the past 8 months.

I have completed it, but I try to make it faster to be used. In order to achieve that I need to be able to insert time values without colons.

I have found several ways for doing that, like using functions such as concacerate, text,replace, left, right, timevalue etc. However, using those types the time value is counted as text. I have found a way to do the calculation even with that, but this happens only when the out time is not later than 23:59.

I have also tried to change time and make it custom ##":"## . Doing that and using some of the above functions I have managed to do the calculations, even if the out time is greater than 23:59, but the times appear as a number eg. 26:30 (it's essential to be put like that if i want the excel to understand that the out time is greater than the in time and do the calculations properly) instead of 2.30 that would be if the cell was formated to be "time 13:30".

I have also tried to use some macros, for example but i really cannot understand how to insert and use them. I have found an excel workbook that does what I want, but it only does it when the time is not greater than 23:59. You can find it at

To sum up, I need a way, to insert time without colons, in the same columns as they appear, and to be counted as time, in order to do the calculations i want. Finally, when the time is later than 23:59, for instance 2:30 pm, to be inserted as 26:30 (2:30 + 24:00) but to be transformed automatically to 2:30 (as excel does on its own).

I apologise for my long query, but i had to explain it in detail. If you have any questions you may contact me even to send you a sample of what i do, which purpose is to help people who work regular hours to calculate the extra money they should get.

Thank you in advance.

Hi. I have a little experience with Excel but I need to do two things beyond my expertise.

(1) I have dropdown boxes and they work fine when the box is opened and an item is selected. After an item is selected it is auto-recognized next time the first letter of a box item is typed. Is there any way to get it to auto-recognized the first time without using the dropdown box. For example, if the drop down box has words ‘blue, red, yellow, green’ it does not recognize ‘blue’ when I type ‘‘b’ until it is first selected from the menu, then subsequent entries of ‘b’ are recognized as ‘blue.’

(2) This one is more complex. I need to do T-V=W where T is military time without colons, V is another military time without colons and W is total hours and minutes. It gets further complicated because the military times can cross over two days. Such as: Tuesday night 2300 – Wednesday morning 0430 = 5:30 total minutes.

Thank you very much for any help you can provide.

For ease of use Iam trying to allow data entry for my wage file to except the time without the user having to type a colon each time. I have found, on this forum a peice of code posted by Dave Hawley (Thank You) that almost gets me there. My problem is that we have a nightshift and and some of the times I need to express are around midnight. while everything works fine for 23:59 I get an error when typing 00:01.
I have to admit that I understand little of the code I am using and hope that it is acceptable to post someone elses code here

    Dim strTime As String 
    If Target.Column = 1 Or Target.Column = 2 Then 
        If IsNumeric(Target) Then 
            Application.EnableEvents = False 
            strTime = Target 
            If Len(strTime) = 3 Then strTime = "0" & strTime 
            Target = TimeValue(Left(strTime, 2) & ":" & Right(strTime, 2)) 
            Target.NumberFormat = "h:mm" 
            Application.EnableEvents = True 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Is there an option in excel to enter military time such as 1300 instead of 13:00? I thought there was but can't seem to find it in the Cell formatting setup.
Or Perhaps a code instead that would allow an entry in cells A1: B30 without the colon? Thanks for the help!

Hi, I have a spreadsheet(database) that I use to import data to. I import the data in a row at the top of my list, so as new data comes in, the time fields are always in cells d2,e2,f2,g2. The users who enter the data on the other sheets i import them from enter the time in 1 of 3 formats:
1)Standard AM/PM time with colons
2)Military time with colons
3)Military time without colons

So when I import the data my coloumns are formatted for AM/PM time, but if the data is in format 3, all I get is 12:00 as the imported time. All other formats, my database is fine with.

When I import the data it is done from a VBA Macro so I would like to add a bit of code to that same macro that will first check the cells being imported for format and if and only if it is in format 3, I want it to add the colon. It does not have to convert it to military time as my database uses the AM/PM format.

Thanks for reading, any help is greatly appreciated.

I just want to shear a formula which will help if you to calculate time stored as text for example

from | to |worked
1800 | 2400| 6

I searched the forum earlier and could not find anything simular
please note that the cell format for column A and B is custom 0000 in case values like 0100 have to be entered

the formula placed in c1 is:

Wow, I'm frustrated. I've searched and found many people looking for solutions to circumstances similar to mine, but have yet to see a definitive answer that I can get to work for my particulars. It seems an issue that should be a basic function of excel as it is likely a popular operation, but apparently it's not.

I need to be able to enter a four digit number into cell A1 representing a military time (**NO COLON**, i.e. 0630) and another number in cell B1 with the same format. I then need to perform a computation on those two times in order to determine the duration between them. I need to output the duration in hours and tenths of hours (i.e. 3:12 would be 3.2 hours). The tenths of hours would be rounded to conform to the template:

Minutes ------- Tenths
1-2 ------------------ .0
3-8 ------------------ .1
9-14 ----------------- .2
15-20 --------------- .3
21-26 --------------- .4
27-33 --------------- .5
34-39 --------------- .6
40-45 --------------- .7
46-51 --------------- .8
52-57 --------------- .9
58-60 --------------- .0

Excel has trouble with time computations that span more than one day (e.g. 2345-0130) otherwise I would have already figured this problem out.

Through long drawn out in cell formulas I've got a manual solution that is close to working for me but also runs into problems given certain specific situations that span more than one day.

I figure there's got to be a solution using macros to do what I want, but unfortunately I don't have the programming knowledge to create working macros.

Can anybody provide help? I would be extremely grateful.

If there's a roundabout way of handling this that anybody can provide I'm open to those suggestions as well.


I have a textbox on a userform for a military time (24 hour clock). The
trouble is that I can't seem to get any format codes to work.

I just want the user to be able to input a 4 digit time and have the input
display in the textbox as a 24 hour short time.

For example: User inputs 1345 and then textbox displays 13:45. The value
then postd to the spreadsheet would be 13:45.

I know this is probably simple, but I can't seem to get anything to work.


Please understand I've only done very basic formulas.

OK, what I need to do :

Make 2 columns of times/hours (XX:XX), with a 3rd column calculating the difference between the two columns to the left of it -- this I can do, no problem. The problem is I need to be able to enter the times quickly, without colons (OK, it's my boss, but please read on).

I've seen that this VBE script will enable me to do this :


    Dim TimeStr As String 
    On Error Goto EndMacro 
    If Application.Intersect(Target, Range("C8:H35", "J8:J35")) Is Nothing Then 
        Exit Sub 
    End If 
    If Target.Cells.Count > 1 Then 
        Exit Sub 
    End If 
    If Target.Value = "" Or Target.Value = "OFF" Then 
        Exit Sub 
    End If 
    Application.EnableEvents = False 
    With Target 
        If .HasFormula = False Then 
            Select Case Len(.Value) 
            Case 1 ' e.g., 1 = 00:01 AM
                TimeStr = "00:0" & .Value 
            Case 2 ' e.g., 12 = 00:12 AM
                TimeStr = "00:" & .Value 
            Case 3 ' e.g., 735 = 7:35 AM
                TimeStr = Left(.Value, 1) & ":" & _ 
                Right(.Value, 2) 
            Case 4 ' e.g., 1234 = 12:34
                TimeStr = Left(.Value, 2) & ":" & _ 
                Right(.Value, 2) 
            Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
                TimeStr = Left(.Value, 1) & ":" & _ 
                Mid(.Value, 2, 2) & ":" & Right(.Value, 2) 
            Case 6 ' e.g., 123456 = 12:34:56
                TimeStr = Left(.Value, 2) & ":" & _ 
                Mid(.Value, 3, 2) & ":" & Right(.Value, 2) 
            Case Else 
                Err.Raise 0 
            End Select 
            .Value = TimeValue(TimeStr) 
        End If 
    End With 
    Application.EnableEvents = True 
    Exit Sub 
    MsgBox "You did not enter a valid time or entered it wrongly.  To enter a time such as 5.30pm type 1730" 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I don't doubt that this is probably a code/script that will work. However, what do I do with it? Do I create a module and just paste it in there and it will immediately effect my Excel file? Do I go within "View Code" (inside VBA) and paste it there? Where do I paste it, and how do I activate it?

Nothing I do seems to activate this code so it effects any cell in my worksheet.

I need to find a way to enter times into excel without having to use a colon.

The data I need in the cell is the hour, the minute, and either AM or PM. No seconds, which I believe is why Chip Pearson’s time VBA procedure doesn’t work for me, and I don’t know enough about VBA to change it for my needs.

So for example, I need to enter in times like 10:09 PM or 12:40 AM or 2:33 AM.

All my times will be entered into Column D.

I have done a ton of searching and tried several different solutions, but none of them allow me to put in AM or PM. If I just put 1009 into a cell with Chip Pearson’s macro, it will display as 10:09 AM, which is great if I need it to be AM. But if I need it to be PM, and I enter in “1009 p”, it will display as “1009 p”, neglecting the colon and not displaying the time correctly.

Does anyone have a solution where I can type in a 3 or 4 digit number (233 or 1240) and either “a” or “p” after it, and have it display as 2:33 PM or 12:40 AM?

P.S. I use Excel 2007, if it matters.


I have searched the board, but cannot find a solution to this specific issue. I am setting up a sheet to perform automatic calculations where the input data would be as follows

start time.......start temp........end time........end temp

Now, the goal is to calculate the time at which the product temperature reached a certain point. For example, if the data input was

start time.......start temp........end time........end temp

and I was calculating for the time when the product was 130 degrees, then the answer would be 1800.

The targets I need to calculate for are 130, 120, and 55.

Now, in addition to this, the time must be in military format but I don't want the technicians to have to enter a colon whenever they input the time.

One of my fellow employees has created such a sheet for me, but the colon must be entered every time, and if the times entered cross over midnight then I get an invalid return.

Any ideas? I am using Excel 97 if it matters.

Thank you in advance for your assistance.

It's a long time since I have used excel and I have after a long illness just got back to an old project. Unfortunately, I have forgotten a lot that I had previously learnt from this forum despite re-reading my previous posts
I have attached a sample created in excel 2010.
Row 5 with times entered with the semi colons works just as needed and the calculations in H,J,K,L work OK.
but the full workbook will need lots of entries so I want to do away with having to put in the time consuming semi colons.
In row 6,I have entered the times without the semi colons and formatted the cells as 00:00.
What my question is, Does anyone know how can I get columns H J K L to do the calculations? in row 6
Thank You

i have created a sheet so that i may enter the date and time into a cell and it will calculate scheduled times in intervals out to ~96hrs. i have the input box formatted as: mm/dd/yy-hhmm. i also have the output boxes formatted the same. example:

in B2 i enter 07/10/07 10:15 PM (the only way it will accept it) in box C2 it outputs 15 minutes past this time, D2 is 30 minutes past, E2 is one hour past, F2 is 1.5 hours past, etc.

this all works fine and it outputs exactly how i want it. the problem is i would like to be able to input as military time as well (07/10/07 2215) instead of having to be so specific with the input as "date, single space, time (with colon), single space, AM/PM".

another problem i have discovered in playing with this sheet has to do with adding 24 hours to a date and time. example:

in B2 i enter 07/10/07-2215 and i want to add 24 hours to this and have it displayed in C2 as 07/11/07-2215, but when i try this it displays 07/10/07-2215 in C2 without increasing the date.

Any help would be greatly appreciated.

Thank you.

I have created a project that finds the difference between multiple
times and then adds up the differences. All of which is formatted in
military time and some times utilize two different days.

I would (actually the boss) would like to have the times entered
without the use of ":" and just straight "hhmm" format, to make it
easier to enter.

I changed the format to reflect hhmm, but when I enter the time it
displays in the cell as "0000" and then in the fz box as a date and
time that is nowhere close to what was entered. Then it does not do
the calculations correctly in the next cells.

If the data is already entered and I change the format to hhmm, it
works just fine, but it won't work to be able to enter it.

Any help would be appreciated, I think I am completely lost with this

See attachment for example of what is being done.

|Filename: time example.doc |
|Download: |

vldavis809's Profile:
View this thread:

I'm trying to format a range of cells so that when I enter...


Excel will interpret it as 1:48pm in military time.

However, I just can't seem to locate the correct format (if it even exists).

Just want to be able to enter the time without the colon and have the system
recognize it as a 24-hour format time.


I'm trying to format a range of cells so that when I enter...


Excel will interpret it as 1:48pm in military time.

However, I just can't seem to locate the correct format (if it even exists).

Just want to be able to enter the time without the colon and have the system
recognize it as a 24-hour format time.


I'm looking for a way to convert a column of cells with military time in them to standard time. They do not have a colon in them so when I try to format cells to time, it does not work. Please see attached sheet. Col A has the orig times, Col C has the desired revisions. Any suggestions?

I have two colums: one with the date, and one with military time in the following format (1325). Excel doesn't recognise 1325 as a time without inserting a colon (eg. 13:25). I have tens of thousands of row entries so converting by hand looks overwhelming.

I want a single colum with the dual date and time format so I can graph variation over time for a period of weeks. I can't figure out a way to convert 24 hour time expressed without a colon to 24 hour format with a colon and I can't figure out how to get excel to change date and time in two colums to date and time in one colum.

Any help would be greatly appreciated. Thanks.

I have been banging my head over this for about a week and i think im over thinking it. This is what i need done.

Im making a worksheet and need people to enter in a start time and an end time, then i need it to tell me how many hours that is as well as a total of how many minutes all together. Basically i need it to break down in minutes for sure.

But it must have military time only, and if you do enter it in as time i don't want them to put in the colon. And my issue is usually if the time is 2200 to 0100, it gives me a negative, i fixed that but then my minutes are messed up. The hours and minutes can be separated or in the same cell.

Right now i have it as Start hour / minutes
End hour / minutes
total hours / minutes
total minutes all together

Plz give me a hand i'd appreciate it.

Any other questions you have for me, message me, i need this asap..plz


Does anyone know how to enter only 4 numbers in a cell and have it show as military time.
output would show 14:25


Hi, I keep getting the error:
Compile Error: Ambiguous name detected: Worksheet_Change
I think I have to merge them together but can not figure it out.
Below is the code I am trying to get working.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' if cell that was changed was not in range named rngTimeEntry then
' exit this subroutine
On Error GoTo EnterTimeError
'A Static variable retains its value even after the procedure is run
'This is to prevent repetitive calls to the change event
Static toContinue As String
If toContinue = "No" Then 'already called for this target
toContinue = True
Exit Sub
End If
toContinue = "Yes"
If Intersect(Target, Range("B29:B47,Q29:Q52")) Is Nothing Then
Exit Sub
End If

' If cell that was changed has value outside range of 0001 - 2400, then
' prompt user to enter military time
If Target.Value = "" Then
Exit Sub
End If
If Target.Value < 1 Or Target.Value > 2400 Then
MsgBox "Enter Times in military format without a colon. Example: enter 1:00 PM as 1300.", vbExclamation
toContinue = "No"
Target.Value = ""
Exit Sub
End If

' Change military time format to HH:MM
toContinue = "No" 'next line again changes target which will again call change event - to prevent that set to No
Target.Value = Format(Target.Value, "00:00")
Exit Sub

Exit Sub

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C51:G58")) Is Nothing Then Else Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value / 100
Application.EnableEvents = True
End Sub

How to just enter the time without having to key in the colon everytime? eg 1555 and get it to displayed in correct format as 15:55 [hh:mm]


Hi all,
I melted my eyes and brain reading through pages in this forum referring to TIME.

In short, I don't want to type Military Time. I tried to create a formula to show PM if the time is greater than 12:00 or AM if it is less as follows:

This returns "AM" no matter what time is placed in K4... even if Military time were entered.  So I changed it to a "

I have a field on a form to enter in a "Time of Purchase"

I need for the time to display on the form AND in the underlying table in 'military time' and I do NOT want to enter AM/PM after inputting the time.

Could someone specify how and where to type something that will display 02:00 (~2a.m.) as '02:00" in BOTH the form AND the linked table?

*I have been using 'Short Time' but to no avail...

The time in the form and the table repeatedly DROPS the leading '0' (zero) and even though I type in '02:00,' it stores it on the form and table as '2:00' and loses the leading '0' (zero).

Thank-you in advance for your time and efforts!

No luck finding an answer? You could always try Google.