PDA

View Full Version : Out of places to turn for Time and date stamp help



grgirrgang
04-16-2018, 11:50 AM
Good afternoon,

i am not sure the the best way to resolve my issue. I am using excel 2016. My set up looks like this in simple spread sheet format.

Column A: bar code scanned or info entered

Column B: Hour, minute, second recorded

Column C: Month/day/year recorded

Column D: Day of the week (i.e, Mon, Tue, Wed)

i have working formulas in the spreadsheet that populate all the information as needed but I need this in table format. When I do format as a table to so that it will replicate the formulas it simply returns the following:

Column A: Data
Column B: 12:00:00AM
Column C: 1/0/1900
Column D: Sat

I am using the following formulas that work in the spreadsheet

Column B formula: =if(A2<>””,if(B2=“”,NOW(),B2),””)

column c formula: =if(A2<>””,if(C2=“”,NOW(),C2),””)

column d formula: =TEXT(C2,”DDD”)

these are all formated correctly and return proper values in spreadsheet but not in Tables. Could someone help me with VBA if that is the solution I know absolutely nothing about VBA but it seems that may be the route I need to go.

any help is more than greatly appreciated!

SamT
04-16-2018, 12:15 PM
PLace this code in the applicable Sheet's Code Page
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then TimeStamp Target 'Calls TimeStamp sub
End Sub

Private Sub TimeStamp(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'More than one cell changed

Application.EnableEvents = False 'Stop these changes from triggering Worksheet_Change sub

With Target
.Offset(, 1) = Format(Now, "hh:mm:ss AMPM")
.Offset(, 2) = Format(Date, "mm/dd/yyyy")
.Offset(, 3) = Format(Date, "DDD")
End With

Application.EnableEvents = True

End Sub

grgirrgang
04-16-2018, 12:34 PM
Thanks a million SamT! Going to give it a shot in an hour or two. Really really appreciate your expertise with this problem!