Military Time Issue

[vldavis809]

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

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

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

If the data is already entered and I change the format to hhmm, i
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 thi
one.

See attachment for example of what is being done

+-------------------------------------------------------------------
|Filename: time example.doc
|Download: http://www.excelforum.com/attachment.php?postid=5189
+-------------------------------------------------------------------

 

[Bernie Deitrick]

Copy the code below, right-click on the sheet tab, ans paste the code in the window that appears.
It will work on any cell that is formatted for hhmm.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
On Error GoTo ErrHandler:
If Target.NumberFormat = "hhmm" Then
Application.EnableEvents = False
Target.Value = Int(Target.Value / 100) / 24 + (Target.Value Mod 100) / 1440
Application.EnableEvents = True
End If

ErrHandler:
Application.EnableEvents = True
End Sub

 

[Mikeopolo]

Take a look at http://www.mrexcel.com/tip029.shtml

This uses a change_event macro, which grabs your input of 1234 and
inserts the colon for you.

THe instructions on this page are fairly good.

Regards
Mike

 

[vldavis809]

Bernie,

That worked great!!!

I have one question, if I enter data and decide it was incorrect, the
delete the data, it now defaults to "0000" and reads as 1200 midnite.
have to delete the entire row to be able to remove the data. I know
can overwrite it, but there may be a time where there is a keystrok
error.

It has to remain blank because there is a formula that reads th
information somewhere else to calculate certain time frames.

Any ideas

 

[Bernie Deitrick]

Select the cell and press delete - don't overwrite it with a zero....

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry, I had changed the code that I gave you. You need to add the line

If Target.Value = "" Then Exit Sub

at the top...

HTH,
Bernie
MS Excel MVP

Select the cell and press delete - don't overwrite it with a zero....

HTH,
Bernie
MS Excel MVP