| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I automatically enter a time and date? | Excel Discussion (Misc queries) | |||
| Type data in one cell - and it automatically populates another cel | Excel Worksheet Functions | |||
| DATE & TIME OF DAY APPEAR AUTOMATICALLY IN CELL | Excel Discussion (Misc queries) | |||
| date and time updated automatically | Excel Worksheet Functions | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Linear Mode
