Time and Date populates automatically
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 |
Time and Date populates automatically
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 clickFormatCellsCustom [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" wrote: 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 |
Time and Date populates automatically
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" wrote: 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 clickFormatCellsCustom [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" wrote: 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 |
Time and Date populates automatically
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" wrote: 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" wrote: 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 clickFormatCellsCustom [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" wrote: 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 |
Time and Date populates automatically
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" wrote: 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" wrote: 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" wrote: 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 clickFormatCellsCustom [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" wrote: 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 |
All times are GMT +1. The time now is 09:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com