Excel formular [Solved/Closed]

Posts
190
Registration date
Tuesday June 18, 2013
Last seen
October 2, 2015
- Mar 10, 2014 at 03:01 PM - Latest reply:
Posts
2447
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 15, 2018
- 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.
See more 

9 replies

Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
- Mar 10, 2014 at 03:49 PM
0
Thank you
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
Posts
190
Registration date
Tuesday June 18, 2013
Last seen
October 2, 2015
- Mar 10, 2014 at 04:34 PM
0
Thank you
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?
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
- Mar 10, 2014 at 07:05 PM
0
Thank you
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.
Posts
190
Registration date
Tuesday June 18, 2013
Last seen
October 2, 2015
- Mar 11, 2014 at 10:01 AM
0
Thank you
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.
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
- Mar 11, 2014 at 10:44 AM
0
Thank you
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
Posts
190
Registration date
Tuesday June 18, 2013
Last seen
October 2, 2015
- Mar 11, 2014 at 10:59 AM
0
Thank you
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,
Posts
2447
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 15, 2018
- Mar 11, 2014 at 11:40 AM
0
Thank you
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

Posts
190
Registration date
Tuesday June 18, 2013
Last seen
October 2, 2015
- Mar 11, 2014 at 12:39 PM
0
Thank you
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.
Posts
2447
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 15, 2018
- 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:
http://answers.microsoft.com/en-us/office/forum/office_2007-excel/privacy-warning-for-office-excel/1b6a8525-445f-4760-addc-91b33c822a7d

Good luck,
Trowa