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
one.

See attachment for example of what is being done.


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

--
vldavis809
------------------------------------------------------------------------
vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
View this thread: http://www.excelforum.com/showthread...hreadid=571361

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



"vldavis809" wrote in message
...

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
one.

See attachment for example of what is being done.


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

--
vldavis809
------------------------------------------------------------------------
vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
View this thread: http://www.excelforum.com/showthread...hreadid=571361

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


--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570
View this thread: http://www.excelforum.com/showthread...hreadid=571361

Bernie,

That worked great!!!

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

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

Any ideas?


--
vldavis809
------------------------------------------------------------------------
vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
View this thread: http://www.excelforum.com/showthread...hreadid=571361

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

HTH,
Bernie
MS Excel MVP


"vldavis809" wrote in message
...

Bernie,

That worked great!!!

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

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

Any ideas?


--
vldavis809
------------------------------------------------------------------------
vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
View this thread: http://www.excelforum.com/showthread...hreadid=571361

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Select the cell and press delete - don't overwrite it with a zero....

HTH,
Bernie
MS Excel MVP


"vldavis809" wrote in message
...

Bernie,

That worked great!!!

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

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

Any ideas?


--
vldavis809
------------------------------------------------------------------------
vldavis809's Profile: http://www.excelforum.com/member.php...o&userid=36146
View this thread: http://www.excelforum.com/showthread...hreadid=571361