need Code for time and date stamp with transfer

[Shawnathon]

I have to update spreadsheets quite often and always have to type in date and time. Need code to populate current date and time, then move it to "last checked" area. Here's my spread sheet lay out:

open - 3 (A2)
pending - 4 (A3)
Current date - (A4)
Current time - (A5)

open - 3 (A7)
pending - 4 (A8)
Date - (A9)
time - (A10)
Last Checked (A11)

Example: This is how it will look before

open - 3
pending - 4
July 1 2011
12:03 A

open - 8
pending - 7
June 30 2011
1:33 P
Last Checked

Then after:

open - 0
pending - 0
Current date
Current time

open - 3
pending - 4
July 1 2011
12:03 A
Last Checked

I would like the top to move down to "last checked" area. Then put in current date & time with changing open & pending to 0.

Thanks in advance.

Shawn

 

[kpark91]

This code should populate the current time and current date.

Range("A9").Value = Format$(Date, "mmm dd yyyy")
Range("A10").Value = Format$(Time, "h:mm:ss AM/PM")

and I do not understand the other parts you have mentioned.

 

[Peter_SSs]

Shawn

Try this on a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> StampAndMove()<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2:A5")<br>        Range("A7:A10").Value = .Value<br>        .Value = Application.Transpose(Array(0, 0, <SPAN style="color:#00007F">Date</SPAN>, Time))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Shawnathon]

Hi Peter, thanks for the help it worked great! I needed to keep the "open & pending" but I figured that out, i just added it to the array:

Sub StampAndMove()
With Range("A2:A5")
Range("A7:A10").Value = .Value
.Value = Application.Transpose(Array("open - 0","pending - 0", Date, Time))
End With
End Sub

it worked! Thanks.

You wouldn't happen to know how to get this to work in google docs spreadsheet? Works fine in excel but when I try it there the code is a little different, just thought I's ask.

One more thing if you can help, I have another thread that i have been waiting on, maybe you can help me with that. Here it is, thanks.

http://www.mrexcel.com/forum/showthread.php?p=2774408&posted=1#post2774408

thanks Peter!

Shawn

 

[Peter_SSs]

I needed to keep the "open & pending" but I figured that out, i just added it to the array:

Ah, I thought they were just descriptions to explain to us what you were doing.
Good that you worked that out yourself though.

You wouldn't happen to know how to get this to work in google docs spreadsheet?

No, not something I know about.

One more thing if you can help, I have another thread that i have been waiting on, maybe you can help me with that. Here it is, thanks.

http://www.mrexcel.com/forum/showthread.php?p=2774408&posted=1#post2774408

I had already looked at that thread and thought it was under control. I'll take another look (no promises) in a while though.

 

[Shawnathon]

Thanks Peter! Appreciate the help!

Shawn