I am trying to make a spreadsheet for work. I am experienced with excel
and VB but cant get 3 items to work on my spreadsheet. Every time
someone brings a item into my shop to be tested it will be added to
this log.
First, I would like to have a date to be automatically entered in cell
G1 and a time in H1 when anything is put in cell C1. I tied the now
function but I would like the date not to change when C2 C3 and so on
are filled out.
Example
A1 B1 Initials(C1) D1 E1 F1 Date(G1) Time(H1)




Second I would like the first row after the header to be blank. After
a person adds a row of data can a new row be automatically be inserted
above it?

Finally I am trying to do a total time in the shop column. In this
column the date and time out would be subtracted from the date and time
in to give me the total time in the shop.
This is what I have now
=IF(G2+I2=0," ",IF(G2+I2=G2,"In Progress",((I2+J2)-(G2+H2))))
but the answer always adds a day. If its only an hour difference in
time and date I get 1//0/00 1:00 for a total time in shop. What am I
doing wrong here.

sorry if this is totaly out there :) but any help would be great.


--
Tuck
------------------------------------------------------------------------
Tuck's Profile: http://www.excelforum.com/member.php...o&userid=34055
View this thread: http://www.excelforum.com/showthread...hreadid=538176

"Tuck" wrote in message
...

I am trying to make a spreadsheet for work. I am experienced with excel
and VB but cant get 3 items to work on my spreadsheet. Every time
someone brings a item into my shop to be tested it will be added to
this log.
First, I would like to have a date to be automatically entered in cell
G1 and a time in H1 when anything is put in cell C1. I tied the now
function but I would like the date not to change when C2 C3 and so on
are filled out.
Example
A1 B1 Initials(C1) D1 E1 F1 Date(G1) Time(H1)



'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 4).Value = Format(Date, "dd mmm yyyy")
.Offset(0, 5).Value = Format(Now, "hh:mm:ss")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


Second I would like the first row after the header to be blank. After
a person adds a row of data can a new row be automatically be inserted
above it?


Change the above to

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(0, 4).Value = Format(Date, "dd mmm yyyy")
.Offset(0, 5).Value = Format(Now, "hh:mm:ss")
End With
ElseIf Target.Row = 2 Then
Target.EntireRow.Insert
End If

ws_exit:
Application.EnableEvents = True
End Sub


Finally I am trying to do a total time in the shop column. In this
column the date and time out would be subtracted from the date and time
in to give me the total time in the shop.
This is what I have now
=IF(G2+I2=0," ",IF(G2+I2=G2,"In Progress",((I2+J2)-(G2+H2))))
but the answer always adds a day. If its only an hour difference in
time and date I get 1//0/00 1:00 for a total time in shop. What am I
doing wrong here.

This works for me. How are you entering the date and time?

thx a lot bob. with the 2 codes you gave me, i only need the bottom one
right? they look the same except that the bottom one incerts the line.
I also got erros and noticed 3 "m"s in the date. i changed it to 2 and
the error dosent show up any more but either way i didnt get the dates
to enter after filling in C1. Im not really sure how the code works so
dont even know where to start trouble shooting it.

I have an equation for the time in shop (shown below) when a new line
is incerted is there a way for that code to show up in the new inserted
line?


Finally I am trying to do a total time in the shop column. In this
column the date and time out would be subtracted from the date and
time
in to give me the total time in the shop.
This is what I have now
=IF(G2+I2=0," ",IF(G2+I2=G2,"In Progress",((I2+J2)-(G2+H2))))
but the answer always adds a day. If its only an hour difference in
time and date I get 1//0/00 1:00 for a total time in shop. What am I
doing wrong here.

This works for me. How are you entering the date and time?


im entering my date and time as 5/2/06 2:00 and 5/2/06 3:00 and i get
the 1/0/00 1:00 as my reply


--
Tuck
------------------------------------------------------------------------
Tuck's Profile: http://www.excelforum.com/member.php...o&userid=34055
View this thread: http://www.excelforum.com/showthread...hreadid=538176

The second one is an extension of the first, it handles input in the row
after the header row, to preserve a blank row, as well as the date time
input.

You shouldn't need to change anything, it works fine as provide, and gives a
date like 02 May 2006. Just enter something in C1 and see what happens.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Tuck" wrote in message
...

thx a lot bob. with the 2 codes you gave me, i only need the bottom one
right? they look the same except that the bottom one incerts the line.
I also got erros and noticed 3 "m"s in the date. i changed it to 2 and
the error dosent show up any more but either way i didnt get the dates
to enter after filling in C1. Im not really sure how the code works so
dont even know where to start trouble shooting it.

I have an equation for the time in shop (shown below) when a new line
is incerted is there a way for that code to show up in the new inserted
line?


Finally I am trying to do a total time in the shop column. In this
column the date and time out would be subtracted from the date and
time
in to give me the total time in the shop.
This is what I have now
=IF(G2+I2=0," ",IF(G2+I2=G2,"In Progress",((I2+J2)-(G2+H2))))
but the answer always adds a day. If its only an hour difference in
time and date I get 1//0/00 1:00 for a total time in shop. What am I
doing wrong here.

This works for me. How are you entering the date and time?


im entering my date and time as 5/2/06 2:00 and 5/2/06 3:00 and i get
the 1/0/00 1:00 as my reply


--
Tuck
------------------------------------------------------------------------
Tuck's Profile:
http://www.excelforum.com/member.php...o&userid=34055
View this thread: http://www.excelforum.com/showthread...hreadid=538176