Excel formular

Solved/Closed
VeasnaYim Posts 190 Registration date Tuesday June 18, 2013 Status Member Last seen October 2, 2015 - Mar 10, 2014 at 03:01 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 11, 2014 at 12:51 PM
Hello Sir,
Can you let me know the formula in Excel:
I have a table as below:

1 A B
Status Date completed
------------------------------------------------------------
2 Complete 3/10/2014


This table, At the column "A,Status" when i Put the word" Completed", it's will automatic put current Date to Column "B, Row 2".
But i don't know after that when tomorrow arrived,at the column "B,Row 2", change date too.

8 replies

Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Mar 10, 2014 at 03:49 PM
VeasnaYim, Good afternoon.

When you use NOW() or TODAY() functions at excel, they give you a dinamic values.

As you want a FIXED time stamp at column B, you must use a CODE (VBA).

Belo Horizonte, Brasil.
Marcílio Lobão
0
VeasnaYim Posts 190 Registration date Tuesday June 18, 2013 Status Member Last seen October 2, 2015 8
Mar 10, 2014 at 04:34 PM
Good after noon, Mazzaropi.
Thanks for your information.
And i have a question more related Excel.

When i put this number to a cell 1717E1 and after that i Enter, i get 17170 into that cel.
What can i fix it?
0
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Mar 10, 2014 at 07:05 PM
VeasnaYim, Good evening.

Initially 1717E1 is not a NUMBER.
It can perhaps be a CODE of a product or something else.

1717 is a NUMBER.
CITY is a TEXT.
1717CITY is an ALPHANUMERIC expression, treated inside EXCEL as a TEXT.

1717B4; 1717C2; 1717D. All of these are TEXTs.

On your specific case, 1717E is a TEXT, BUT...1717E3 is not.

EXCEL treat this as a MATH Expression because the "e" or "E".
Then, if after "e"or "E" there is some digit, suppose 3 or 1, the final result will be: TEXT plus zeros.

1717E = 1717E
1717E1 = 17.170
1717E3 = 1.717.000
1717E7 = 17.170.000.000

If you want use this as a code for something, format the cell or cells to TEXT.
After it retype the code.

Please, tell us if it worked for you.
Fell free to ask anything you need.

I hope it helps.
0
VeasnaYim Posts 190 Registration date Tuesday June 18, 2013 Status Member Last seen October 2, 2015 8
Mar 11, 2014 at 10:01 AM
Dear Mazzaropi,
Yes It worked when i formatted that cell with text.
Back to the formula =now() or today(), If it's not take long can you tell me how to use vb code to make current date , not change when the tomorrow is come?
Thanks you so much.
0

Didn't find the answer you are looking for?

Ask a question
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Mar 11, 2014 at 10:44 AM
VeasnaYim, Good morning.

Unfortunately I don't know how programming Excel VBA.

When you use a CODE, your data stay FIXED.
If the day you stamped the date is 03/11/2014 on 03/11/2014, when you open your file on 03/30/2014 that cell stays 03/11/2014. Don't change.

Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
VeasnaYim Posts 190 Registration date Tuesday June 18, 2013 Status Member Last seen October 2, 2015 8
Mar 11, 2014 at 10:59 AM
I was studied about Excel Advanced but right now i forget at all.
No problem about this that you can't but thanks so much for your caring me about my issues.

May God bless you.
Best Regards,
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 11, 2014 at 11:40 AM
Hi guys,

Maybe I can give some input.

Implement the following code by right-clicking the sheets tab and click on "View code". Paste the following in the big white field:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
If Target.Value = "Completed" Then Target.Offset(0, 1).Value = Date
End Sub

Whenever "Completed" is entered in column B, today's date is entered in the cell to the right.

If alterations are desired, let me know.

Best regards,
Trowa

0
VeasnaYim Posts 190 Registration date Tuesday June 18, 2013 Status Member Last seen October 2, 2015 8
Mar 11, 2014 at 12:39 PM
Hello TrowaD,
Thanks and it work on only current and i can't save because when i hit save it said:
Privacy warning: this document containing Macro, Active Control X, XML expension xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Please help.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 11, 2014 at 12:51 PM
Hi VeasnaYim,

That never happened to me, but a google search let me to this link which provides some solutions to try:
https://answers.microsoft.com/en-us/office/forum/office_2007-excel/privacy-warning-for-office-excel/1b6a8525-445f-4760-addc-91b33c822a7d

Good luck,
Trowa
0