stamp time in excel tables

[karim elb]

i am working with tasks in excel table -for example (row 1contains task -to answer i insert row 2 under the task above)-i want to - time stamp in D2
- i hope the answer with formulas -i am beginner in macro
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]

F
​

[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]task:how to stamp time ....
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]formula?[/TD]
[TD][/TD]
[TD]answer:..........[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thankful because i tried and read many ways without
usefulness

 

[Fishboy]

Hi help pls

my timestamp give an error #n/a when i reopened the excel file



=if(ab8<>"", if(u8="",now(),u8), "")

Hi gongmarley, welcome to the boards.

Based on what little information you have shared, I cannot recreate the issue at this end. If I create a new workbook and use the formula you provided above then after savibng, closing and re-opening the workbook I did not get an error. It is also worth noting that using a formula to get a date stamp is unreliable as the formulaic version of Now() will update to whatever the current date is based on your computer's clock every day rather than being a static date.

Can you give us more information about your data set and requirements?

 

[gongmarley]

Hi gongmarley, welcome to the boards.

Based on what little information you have shared, I cannot recreate the issue at this end. If I create a new workbook and use the formula you provided above then after savibng, closing and re-opening the workbook I did not get an error. It is also worth noting that using a formula to get a date stamp is unreliable as the formulaic version of Now() will update to whatever the current date is based on your computer's clock every day rather than being a static date.

Can you give us more information about your data set and requirements?

thanks for reply my pm sir, i use this formula to get timestamp:

=IF((F7="GOOD")*(G7="up")*(L7="BUY")*(K7>150),"BUY",IF((F7="GOOD")*(G7="DOWN")*(L7="SELL")*(M7>150),"SELL",IF((L7="SELL")*(K7>-200)*(M7>200),"SCALPS",IF((L7="BUY")*(K7>200)*(M7>-200),"SCALPB",""))))

and this code for timestamp :

=IF(AB7<>"", IF(U7="",NOW(),U7), "")

the problem is when i reopened back excel at timestamp colum give an #n/a not the history of timestamp earlier so here i want my timestamp have history when i saved and open back i dont want actual pc time when i reopen i want history...

tq

 

[Fishboy]

thanks for reply my pm sir, i use this formula to get timestamp:

=IF((F7="GOOD")*(G7="up")*(L7="BUY")*(K7>150),"BUY",IF((F7="GOOD")*(G7="DOWN")*(L7="SELL")*(M7>150),"SELL",IF((L7="SELL")*(K7>-200)*(M7>200),"SCALPS",IF((L7="BUY")*(K7>200)*(M7>-200),"SCALPB",""))))

and this code for timestamp :

=IF(AB7<>"", IF(U7="",NOW(),U7), "")

the problem is when i reopened back excel at timestamp colum give an #n/a not the history of timestamp earlier so here i want my timestamp have history when i saved and open back i dont want actual pc time when i reopen i want history...

tq

You will not be able to do what you describe with formulas alone for the reasons I have outlined above. If you put =Now() into a cell today then that cell will show today's date, but tomorrow it will show tomorrow's date as it is reading directly from the computer clock.

If you need the time stamp to remain static, meaning if it shows 08/07/16 today it will still show 08/07/16 tomorrow then you will need to use VBA. By the sounds of it you want it to record when you last saved so that the next time you open the workbook you have a historic record of when it was last saved. If that is correct then you could try the following basic code. To test it out you need to press ALT+F11 to open the VBA Developer window. Next find the name of your workbook in the Project pane in the top left hand corner. Once you find it just right-click on ThisWorkbook and select View Code. In the new window that opens simply copy and paste in this code (you can change the bold red part to correctly reflect the sheet name and cell reference you want the time stamp in):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("Sheet1").Range("A1").Value = Date + Time

End Sub

You will need to save the document as a macro enabled workbook (.xlsm format). Once this is done the specified cell will update with the current date and time each time you save the workbook. When you next open the workbook the right date and time will still be there.

I hope this helps.

 

[gongmarley]

You will not be able to do what you describe with formulas alone for the reasons I have outlined above. If you put =Now() into a cell today then that cell will show today's date, but tomorrow it will show tomorrow's date as it is reading directly from the computer clock.

If you need the time stamp to remain static, meaning if it shows 08/07/16 today it will still show 08/07/16 tomorrow then you will need to use VBA. By the sounds of it you want it to record when you last saved so that the next time you open the workbook you have a historic record of when it was last saved. If that is correct then you could try the following basic code. To test it out you need to press ALT+F11 to open the VBA Developer window. Next find the name of your workbook in the Project pane in the top left hand corner. Once you find it just right-click on ThisWorkbook and select View Code. In the new window that opens simply copy and paste in this code (you can change the bold red part to correctly reflect the sheet name and cell reference you want the time stamp in):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("Sheet1").Range("A1").Value = Date + Time

End Sub

You will need to save the document as a macro enabled workbook (.xlsm format). Once this is done the specified cell will update with the current date and time each time you save the workbook. When you next open the workbook the right date and time will still be there.

I hope this helps.

ok sir let me try 1st then i will comment if the vba works for me

many thanks sir

 

[Fishboy]

ok sir let me try 1st then i will comment if the vba works for me

many thanks sir

No worries, I hope this helps.

 

[gongmarley]

one more thing sir i forgot to imform that i want my result timestamp see in another cell which mean AB7:AB33<> then the result in U7:U33(timestamp here) acording to my formula

 

[Fishboy]

one more thing sir i forgot to imform that i want my result timestamp see in another cell which mean AB7:AB33<> then the result in U7:U33(timestamp here) acording to my formula

Do you mean that for each cell in range AB7:AB33 where the value is not blank then the corresponding cell in range U7:U33 should each have a time stamp?

 

[gongmarley]

No worries, I hope this helps.

Do you mean that for each cell in range AB7:AB33 where the value is not blank then the corresponding cell in range U7:U33 should each have a time stamp?

yes sir the result in another column(U7:U33)

 

[Fishboy]

yes sir the result in another column(U7:U33)

Maybe something like this then, installed in the same way as I described before. Update the bold red sheet name to reflect the sheet you wanted updated:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Defines variable
Dim Cell As Range


' On saving, for each cell range AB7 to AB33 on the specified sheet
For Each Cell In Sheets("YourSheetName").Range("AB7:AB33")
    ' If the cell value is not blank then...
    If Cell.Value <> "" Then
        ' Update the corresponding cell in column U of the specified sheet with the date and time
        Sheets("YourSheetName").Range("U" & Cell.Row).Value = Date + Time
    End If
' Check next cell in range
Next Cell


End Sub