Need an elapsed date/time formula!

briantemp

New Member
Joined
Apr 7, 2004
Messages
19
Hello again... you were all so much help before, I have another question for youz:

In one cell I need to enter this:

1-1-04 20:45

and in the next cell over I need to enter this:

1-3-04 21:00

...with the third cell displaying the elapsed hours and minutes. Easy right? Well here's the rub:

I'm using this VBA formula to quick-enter military time (with an automatic 24hr clock conversion):

Private Sub Worksheet_Change(ByVal Target As Range)
UserInput = Format(Target.Value, "0000")
If Len(UserInput) > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

In conjunction with this, I'm using this formula to calulate the elapsed time:

=C2-B2+IF(B2>C2,1)

The question is how can I alter the formula(s) in order to be able to type this:

1-1-04 2045

...in the first cell, and this:

1-3-04 2100

...in the second cell to get the third cell to correctly display the elasped time, factoring in the elapsed days? I only need the result cell to show [h]:mm if that helps?

Sorry if all the above is redundant, I just want to give you guys all the info I can to help speed the solution.

Thanx again, hope to hear from you!

~Brian
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
The issue is that you are changing the format of all of the cells. Try this as your event handler

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Then
UserInput = Format(Target.Value, "0000")
If Len(UserInput) > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub
 
Upvote 0
<The issue is that you are changing the format of all of the cells. Try this as your event handler>

Hmmm... I tried your formula but it didn't have the desired result. The catch might be that I need to speed-enter the data, which means not having to type in the colon when I enter the time as military time. I can put the hyphens in the date easily enough, so that's not the issue.

Any other ideas?

Thanx!
 
Upvote 0
Brian,

The following may work for you as you are in the US. When running the macro locally, the macro forces all my regional dates to the US format (mm/dd/yy) i.e. the Format function is US-centric.

I also note that the macro presented by you will formatted military time to real time in all cells in a worksheet. It is assumed that you would not really want this because when you insert your formula, the macro tries to convert that to time. The revised macro covers columns B and C, rows 1 to 10 (adjust to suit).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRng1 As Range, MyRng2 As Range

Set MyRng1 = Range("B1:B10")
Set MyRng2 = Range("C1:C10")
If (Not Application.Intersect(Target, MyRng1) Is Nothing Or _
    Not Application.Intersect(Target, MyRng2) Is Nothing) Then

  UserInput = Format(Target.Value, "mm/dd/yy 0000")
             
  If Len(UserInput) > 1 Then
  NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
  Application.EnableEvents = False
  Target = NewInput
  Application.EnableEvents = True
  End If
End If

End Sub
HTH

Mike
 
Upvote 0
Thank you Ekim! I'm halfway there... I used your macro formula and it does the calculation for me, the only glitch is that when I enter the following information:

01/01/04 2045

...it displays the correct info in the Formula Bar as:

1/1/2004 08:45:00 PM

...which is perfectly suitable for my needs. However, in the entry cells the time is shows as (pertaining to the above example):

1/1/2004 911688:00

I've formatted the entry cells as:

mm/dd/yy [hh]:mm

What should/can I change to make it display the correct info after I type it in?

Thanx! :)
 
Upvote 0
Hey, I think I figured it out! All I had to do was reformat the data cells in the "Format - Date" category and it snapped everything into place!

Thanks again everyone... I appreciate your help! (y)

~Brian
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top