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

 

[MrKowz]

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

I'll take a peek at this later this evening. I'm on a work device right now, and can't download unauthorized files.

 

[MrKowz]

Alrighty, I downloaded your spreadsheet; see below for the code which will accomplish what you need. To use the code, right click on the worksheet tab and click on "View Code". Copy/paste the below code into the window that pops up.

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B:C, E:F, H:I, K:L, N:O, Q:R, T:U")) Is Nothing And target.Count = 1 And target.Value <> "" Then
    With target
        If .Row >= 7 And .Row <= 27 And .Row Mod 2 = 1 And Format(.Value, "0000") Like "####" Then
            Application.EnableEvents = False
            .Value = Left$(Format(.Value, "0000"), 2) & ":" & Right$(.Value, 2)
            Application.EnableEvents = True
        End If
    End With
End If
End Sub

This code monitors odd numbered rows between row 7 and 27 in columns B, C, E, F, H, I, K, L, N, O, Q, R, T, U. It will automatically convert the entered value from military time to an actual time value. For 00:00 through 09:59, you can omit the leading 0 (thanks Mikerickson for that code which helped to solve that problem!).

Another thing that could help your overall spreadsheet efficiency is calculating the time difference. In columns D, G, J, etc where you are manually entering in the "#" (I'm assuming time between Out and In), try using =(OUT-IN)*24. For example, in D7, use =(C7-B7)*24

Hope this helps, and thank you for your patience!

 

[TonyUK72]

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?

Just a quick question if I may, can you run XLSM files (VBA Macro Enabled) on your GOV computer? If you can are you aware of the method of sending and receiving files (not executable's)? If you don't know how I can send you a private message with details.

If you can only use formulas make sure your start & stop time cells are formatted as TEXT and enter this formula where you would like the result:

=(LEFT(B3,2)&":"&RIGHT(B3,2))-(LEFT(A3,2)&":"&RIGHT(A3,2))-"00:30:00"

 

[DjMikeWatt]

Just a quick question if I may, can you run XLSM files (VBA Macro Enabled) on your GOV computer? If you can are you aware of the method of sending and receiving files (not executable's)? If you don't know how I can send you a private message with details.

I should be able to do this... I'm not on a GOV computer. As far as sending files, I have dropbox. What are you suggesting?

 

[DjMikeWatt]

Is there some kind of simple script I can run that just tells the workbook that anytime I write 4 consecutive digits in specified rows, it should automatically autoformat those 4 digits into the "xx:xx" format (by inserting the colon in between)? That seems like it should be pretty simple, no? In this example, I would still like the cell formatted as TIME so that it will translate it into "11:30 PM" format.

 

[DjMikeWatt]

Ah, ok... so I did this is a round-about kind of way. I entered values in the auto correct settings to change "0500" to "05:30". I did this 48 times, once for each 30-minute interval in a day. My times are never anything other than top or bottom of the hour, so this changes the number into a readable time format for Excel.

It's clumsy, and it may cause me a problem in the future if I need to input one of those numbers without the colon, but for now, it works and it accomplishes what I need it to.

Thanks, everyone.

 

[DjMikeWatt]

Alright... sorry about the stream of posts... but I have this mostly figured out - running into one last issue.

On the worksheet (https://www.dropbox.com/s/grrmjx6yyl03xjs/Encore 1227 Schedule Ending 2017-07-21.xlsx?dl=0), the last column is supposed to total the hours for the week for each row. It's not working. I understand (sort of) why it's not working, but I'm not sure how to format (or formula) that column to make it perform as I want. For example, cell W9 should say "38", as a total of the figures from row 9, under each "#" column. What can I do to properly format column W to make it display that properly?

 

[MARK858]

Excel Workbook
	W
4	TOT
5
6
7	40.00
Sheet1

Excel 2010

Cell Formulas
Range	Formula
W7	=SUM(D7,G7,J7,M7,P7,S7,V7)*24

 

[DjMikeWatt]

Sheet1[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]W[/TH]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: center"]TOT[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="bgcolor: #F2F2F2, align: center"]40.00[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
W7	=SUM(D7,G7,J7,M7,P7,S7,V7)*24

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

Thank you - that is helpful! However, try that on row 9. You'll see that it's not taking into account the fact that the individual "#" columns automatically take out a 30 minute lunch break when the shift is at least 8.5 hours long. So, the total is showing as 39.5 instead of 38. Is there a way I can do calculations on the digit in the cell instead of on the formula data in the cell? Or, any other suggestions?

 

[MARK858]

The sum is correct, you have already been told that changing the format does not change the value only the appearance.

Change your formulas to the below...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
9	Oscar Marrero	1:30 PM	7:30 PM	6:00:00	*	*	0:00:00	8:00 AM	4:00 PM	8	9:00 AM	5:30 PM	8	7:30 AM	4:00 PM	8	9:00 AM	5:30 PM	8	*	*	0	38.00
Sheet1