Excel formular

Solved/Closed
VeasnaYim Posts 180 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 December 27, 2022 - 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.
Related:

8 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
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
VeasnaYim Posts 180 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?
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
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.
VeasnaYim Posts 180 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.
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
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
VeasnaYim Posts 180 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,
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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

VeasnaYim Posts 180 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.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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