Accept Military Time without a colon ":"

[DjMikeWatt]

Hi there,

Here is the my situation. I want to enter time into the spreadsheet as military time. Typing 1445 is much faster than 14:45, and it's insanely faster than 2:45 PM. The problem is, that Excel forces me to use the colon on military time (even though military time doesn't actually use a colon). People say, "Excel doesn't know it's a time without the colon", which is complete nonsense because you can format the cell as TIME. That alone tells it that you're entering time.

So... is there a way that I can tell excel to handle cells like this:

I type It displays
0945 9:45 AM
1130 11:30 AM
1400 2:00 PM

But, at the same time, is able to do time calculations on the cells (=C1-A1-30), where A1 is a start time and C1 is an end time (and 30 is just a break being taken out).

This is what I want to do and, while I am completely amazed that this isn't possible by default in Excel, I believe that there is probably a way around it... hoping to find that way here.

THANKS

 

[Jonmo1]

Welcome to the board.

Try

=TEXT(A1,"00\:00")+0

 

[shg]

Via formatting, values that look like times but actually aren't: https://answers.microsoft.com/en-us...ith-hhmm/6feb0a98-4d47-e011-90b6-1cc1de79d2e2

Converting to actual times, via VBA: https://excel.tips.net/T002412_Entering_or_Importing_Times_without_Colons.html

 

[MrKowz]

Welcome to the forums!

Couple ways we can go about this; we can either use a helper column to convert your entered value into a time value, or we can use a macro which will automatically convert the value after you enter it.

The reason Excel doesn't just recognize "0954" as "09:54" is because time is actually a decimal representation of a date. When you enter a whole number, Excel is seeing that as a date; when you put the colon in, Excel can recognize that as a time and do the automatic conversion for you. Cell formatting only adjusts the display of a cell, it doesn't affect the actual value stored.

That being said, let me know which method you'd prefer, and I'll be happy to come up with a solution.

Cheers!

 

[mikerickson]

You could put this in the code module for the sheet in question.
The "which ranges does this apply to" test, .Column = 4, could be altered to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 4 And .Cells.Count = 1 Then
            If Format(.Value, "0000") Like "####" Then
                If .Value < "2401" Then
                Application.EnableEvents = False
                .Value = Left(Format(.Value, "0000"), 2) & ":" & Right(.Value, 2)
                Application.EnableEvents = True
                End If
            End If
        End If
    End With
End Sub

 

[DjMikeWatt]

Welcome to the forums!

Couple ways we can go about this; we can either use a helper column to convert your entered value into a time value, or we can use a macro which will automatically convert the value after you enter it.

Cheers!

Thank you for the response (and welcome). I think that a macro to convert after it's entered could be a good way to go... that would allow me to type 1445 and have it convert to 2:45 PM? That sounds ideal.

 

[MrKowz]

Thank you for the response (and welcome). I think that a macro to convert after it's entered could be a good way to go... that would allow me to type 1445 and have it convert to 2:45 PM? That sounds ideal.

Check out the reply from mikerickson - this should accomplish exactly what you need, though you may need to adjust the code to fit your actual workbook layout. If you need assistance with that, please let us know what column (or cell) you will be entering the time in, and we can help adjust accordingly.

 

[DjMikeWatt]

Check out the reply from mikerickson - this should accomplish exactly what you need, though you may need to adjust the code to fit your actual workbook layout. If you need assistance with that, please let us know what column (or cell) you will be entering the time in, and we can help adjust accordingly.

I actually don't know where/how to input that code. My sheet is a work schedule... one set of cells would be B7 (time in), C7 (time out), and D7 (number of hours in shift). I assume I can copy and paste that into the other cells and it will adjust?

 

[MrKowz]

I actually don't know where/how to input that code. My sheet is a work schedule... one set of cells would be B7 (time in), C7 (time out), and D7 (number of hours in shift). I assume I can copy and paste that into the other cells and it will adjust?

Do you know what all cells you will be entering the military time? The code is VBA code - we will need to figure out either a range of cells or a pattern of cells that you'll be entering the times into. From there, the code is put into the worksheet's VBA code module (right click on the sheet tab and click "View Code").

For example, will you be entering time in a range like B7:B20, C7:C20? Will it be B7, C7; then B10, C10; then B13, C13; and so on?

 

[DjMikeWatt]

Do you know what all cells you will be entering the military time? The code is VBA code - we will need to figure out either a range of cells or a pattern of cells that you'll be entering the times into. From there, the code is put into the worksheet's VBA code module (right click on the sheet tab and click "View Code").

For example, will you be entering time in a range like B7:B20, C7:C20? Will it be B7, C7; then B10, C10; then B13, C13; and so on?

Here is a copy of the spreadsheet... does this help you understand what I'm doing?

https://www.dropbox.com/s/s4eqrf31qjtxr8a/Encore 1227 Schedule Ending 2017-07-21.xlsx?dl=0