Excel formularSolved/Closed

Question

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.

Mazzaropi · Answer

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

VeasnaYim · Answer

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?

Mazzaropi · Answer

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.

VeasnaYim · Answer

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.

Mazzaropi · Answer

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

VeasnaYim · Answer

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,

TrowaD · Answer

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 = DateEnd 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

VeasnaYim · Answer

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.

Excel formular

8 responses

Similar discussions

Subscribe To Our Newsletter!