Time and Date populates automatically

N

newbie_010108

Hi,
how can i make col m autopopulates with a date and time whenever col k is
populated with info and col N populates date and time whenever col L has an
info. Col O will compute for the time lapse between col m and n. on the 2nd
tab, will be the employee # of working hours summary.
1st tab detail:
Col K - Employees name
Col L - Work Status
Col M - Time Started
Col N- End Work
Col O - Time Lapse
 
J

Jacob Skaria

You will need to use a macro. Try the below and feedback

Right click the sheet tab. View code and paste the below code. If you are
new to macros set the Security level to low/medium in (Tools|Macro|Security)
and reopen excel application and try..Custom format Col O to display the time
difference..

Select ColO. Right click>FormatCells>Custom [h]:mm:ss

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Count = 1 Then
If Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now
If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
 
N

newbie_010108

it works. but the time does not change whenever there is a change in each
field. Like for example if i modify the col K, col M will remain the same and
will not reflect the time that col k was changed.



Jacob Skaria said:
You will need to use a macro. Try the below and feedback

Right click the sheet tab. View code and paste the below code. If you are
new to macros set the Security level to low/medium in (Tools|Macro|Security)
and reopen excel application and try..Custom format Col O to display the time
difference..

Select ColO. Right click>FormatCells>Custom [h]:mm:ss

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Count = 1 Then
If Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now
If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


newbie_010108 said:
Hi,
how can i make col m autopopulates with a date and time whenever col k is
populated with info and col N populates date and time whenever col L has an
info. Col O will compute for the time lapse between col m and n. on the 2nd
tab, will be the employee # of working hours summary.
1st tab detail:
Col K - Employees name
Col L - Work Status
Col M - Time Started
Col N- End Work
Col O - Time Lapse
Click to expand...
Click to expand...
 
J

Jacob Skaria

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Count = 1 Then
Target.Offset(0, 2) = Now

If Target.Column = 11 Then
If Target.Offset(0, 3) <> "" Then
Target.Offset(0, 3) = Now
Target.Offset(0, 4) = 0
End If
End If

If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


newbie_010108 said:
it works. but the time does not change whenever there is a change in each
field. Like for example if i modify the col K, col M will remain the same and
will not reflect the time that col k was changed.



Jacob Skaria said:
You will need to use a macro. Try the below and feedback

Right click the sheet tab. View code and paste the below code. If you are
new to macros set the Security level to low/medium in (Tools|Macro|Security)
and reopen excel application and try..Custom format Col O to display the time
difference..

Select ColO. Right click>FormatCells>Custom [h]:mm:ss

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Count = 1 Then
If Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now
If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


newbie_010108 said:
Hi,
how can i make col m autopopulates with a date and time whenever col k is
populated with info and col N populates date and time whenever col L has an
info. Col O will compute for the time lapse between col m and n. on the 2nd
tab, will be the employee # of working hours summary.
1st tab detail:
Col K - Employees name
Col L - Work Status
Col M - Time Started
Col N- End Work
Col O - Time Lapse
Click to expand...
Click to expand...
Click to expand...
 
N

newbie_010108

thanks jacob! but i need to make the col M, N and O set as protected column
but whenever im protecting this columns error occurs. Can you help resolve
this issue? I need them to be protected so that user can't modify those col
and pls take note that the file is shared.

Thanks!

Jacob Skaria said:
Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Count = 1 Then
Target.Offset(0, 2) = Now

If Target.Column = 11 Then
If Target.Offset(0, 3) <> "" Then
Target.Offset(0, 3) = Now
Target.Offset(0, 4) = 0
End If
End If

If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


newbie_010108 said:
it works. but the time does not change whenever there is a change in each
field. Like for example if i modify the col K, col M will remain the same and
will not reflect the time that col k was changed.



Jacob Skaria said:
You will need to use a macro. Try the below and feedback

Right click the sheet tab. View code and paste the below code. If you are
new to macros set the Security level to low/medium in (Tools|Macro|Security)
and reopen excel application and try..Custom format Col O to display the time
difference..

Select ColO. Right click>FormatCells>Custom [h]:mm:ss

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Count = 1 Then
If Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now
If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


:

Hi,
how can i make col m autopopulates with a date and time whenever col k is
populated with info and col N populates date and time whenever col L has an
info. Col O will compute for the time lapse between col m and n. on the 2nd
tab, will be the employee # of working hours summary.
1st tab detail:
Col K - Employees name
Col L - Work Status
Col M - Time Started
Col N- End Work
Col O - Time Lapse
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time/priority problem 2
Using the IF and Sheets 0
Transpose 4 Columns data to Rows for Employee Time In and Time Out Schedule 0
Possible getpivotdata with offset? 1
Automatic Time and Date 10
Time-table 2
Re posting - Transpose employee data from Columns to Rows 0
Two Toggle Buttons to calculate Day(s) Hour(s) Minute(s) between entry time 1

Top