Calculating Date and Time in different Cells

[mwvirk]

wait i'm modifying in advance mode

 

[mwvirk]

it might be sound crazy for asking such favor but i really need to solve my issue:
(i was trying to attach screen shot but its not working here)

i have drop down menu in I4 with lot of options. based on selection there are two outputs in cell K4 (full / partial) and its so far working fine.

i need to go further more advance and as soon as i select option in I4 it should give me "Full" or "Partial" K4 i want present date in L4 and N4 present time when issue was reported (these dates and time should not change at all. even i open my sheet after months) - then in W4 a timer should start from 00:00:00 - then user will be entering the actual time when problem was started in M4 (all should done immediately after selecting the option from drop down menu in E4 - only M4 will be entered manually)

it is 100% sure that user might noticed problem after 5 or 10 or 30 minutes. lets say user noticed it after 10 min - since timer was started from 00:00:00 and M4 was updated 10 min after the actual problem, then timer in W4 should updated like 00:10:00

now coming to resolve the problem. in U4 user will manually enter the time when problem was resolved (assuming that user was informed late - so, need manual time entry here) Once user enter U4 with time, V4 should be filled up will present date (and shouldn't be changed by closing/opening the sheet later)

and finally W4 should be updated from time when problem was started and when it was resolved. (need to take those 2 times which were manually entered by user in M4 and U4)

i got 250 types of machines so i need same script/macro for all rows. it is also possible that 1 machine is down twice or more than that, in 1 day. so user is copying/pasting(inserting) row.

please help. i know its not difficult but i'm not good in VB and just know basics of Excel.

Thanks

 

[mwvirk]

unfortunately there is still no reply for my post. please see below script where i managed to solve issue for some of the cells mentioned above. pls see if someone can alter the script and help me to get clock in W4

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim irow As Long
If Not Intersect(Target, Range("I:I")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
'if there is a date in column L end the macro

If IsEmpty(Target) Then
Target.Offset(0, 3).ClearContents
Target.Offset(0, 5).ClearContents
GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
Target.Offset(0, 3) = Date
Target.Offset(0, 5) = Time
End If
EndProc:
Application.EnableEvents = True
End Sub