Date stamp

[TheNamelessPoet]

Hello everyone. I am looking for some help on what I am sure is simple but here it goes.

PO numer Acknowledge
4501475751 Y 10/31/2014
4501471230 Y 10/31/2014
4501475877 y 10/31/2014
4501475142 Y 10/31/2014
4501471278 y 10/31/2014
4501477016 Y 10/31/2014
4501477024 Y 10/31/2014
4501477871 Y 10/31/2014
4501478432 y 10/31/2014
4501478466 y 10/31/2014
4501478480 y 10/31/2014
4501478487 y 10/31/2014
4501407817 y 10/31/2014
4501481421 Y 10/31/2014


Above is my basic spreadsheet that is suprisingly becomeing my bosses favorite thing after about 2 days lol.

this is the formula in the 3rd column:

=IF(ISBLANK(A2)," ",TODAY())

As you can see I am trying to make the date the line is entered appear once the line has been populated.
The issue I have is every time you open the document, it updates to todays date.

I am sure ites because I am using teh =Today() function but how do I make it keep the date it was entered. I know it seams lazy but I dont want to have to type in the date. Some of teh people I work with are not computer savy so the least amout that they have to do is better.


The second column is sortable so that you can see what PO is not acknowledged and only those. This spreadsheet, (along with my other coworkers) updates to a master list that has the same properties, which is a whole other question on that sheet but I'll tackle one at a time.

[alansidman]

If you can live with the date being in text format versus a date/time format then try this: =IF(ISBLANK(A2)," ",TEXT(TODAY(),"mm/dd/yyyy"))

Edit: Ignore this suggestion. I tested and it does not hold water.

[TMS]

@Alan: why would that formula not have the same outcome as the original, except as text? What am I missing?


It can be done, but ... see: I need a static date/time in Excel

Helpful Member! xlhelp (Instructor)
18 Sep 09 11:13

Please use with caution.

There is a way to have static date stamp using formulas and iterative function of Excel. The problem arises if there is indeed a circular reference in one of the other formulas besides the date stamp.

But, if you must, then here is the solution.

Suppose you want the static date stamp in cell B2.

First, go to Tools; Options; Calculation and put a check mark in the "Iteration" check box. Then, go to cell B2 and enter the following formula:

=IF(A2="","",IF(B2="",NOW(),B2))

As long as cell A2 is blank, there will be nothing in cell B2; but, as soon as there is an entry in A2, B2 will have static Date and time stamp.

You may need to reformat cell B2.

There are, no doubt, countless references to this method; this is just the first I found. Personally, I'd prefer the VBA approach.


Regards, TMS

[ChemistB]

This can be done by playing with the iteration options or through VBA. VBA is the easiest and safest (IMHO). Right click on the sheet tab >"View Code" and paste this code into the white text box. Then close the VBA editor (no need to save). You will need to save your workbook as an xlsm or xls file.This code will enter the date into column C whenever you enter or change something in column A

Please Login or Register  to view this content.

[TMS]

See also this thread in this forum: Static Date Stamp without using a macro?

[Fotis1991]

I used this many times without any problem.

[alansidman]

@Trevor: My edit comment indicates that it fails as it does act exactly the same as if it were a date format. Maybe the colloquial "does not hold water" is an unfamiliar term in the UK. It is a line from the movie, "My Cousin Vinnie." meaning it is not valid.

[TMS]

@Alan: sorry, completely missed the edit. I think my mind was probably locked on the question of how it was different and didn't move beyond the formula. I'd say it was my eyesight but I had a test this morning and they're OK

No, "does not hold water" is meaningful over here too ... so I did understand it. From what I can gather, there may have been earlier references than "My Cousin Vinnie"

Regards, TMS

[ChemistB]

A good deal further back, I believe it originates from the Old Testament of the Bible, Jeremiah 2:13
"My people have committed two sins: They have forsaken me, the spring of living water, and have dug their own cisterns, broken cisterns that cannot hold water." The broken cisterns was the worship of religious idols.

[TMS]

@ChemistB: yes, I saw that reference, too.

[alansidman]

@ChemistB--Thanks for that information.

[TheNamelessPoet]

Thanks for the quick responses guys :-)
=IF(A2="","",IF(D2="",NOW(),D2))
When I use this formula I just get an answer of "0"

Edit: Oops I didnt finish typing.
this is a basic If, Than, Else formula corect?

Also I have 2007 version so I dont seem to have an option to get to the "iteration"

[TMS]

Basically, you just have a circular reference.

Click on the big Office logo/icon. Select Excel Options near the bottom and then Formulas. Tick Enable Iterations.

[TheNamelessPoet]

Basically, you just have a circular reference.

Click on the big Office logo/icon. Select Excel Options near the bottom and then Formulas. Tick Enable Iterations.

I can't believe thats the only place I didn't look. lol
Thank you for the help guys!

I do have another question but I think this is more complicated. Ill post it here but if it needs its own thread let me know.

The office I work in, we have 4 people we each have a spreadsheet. I then have a master list that all 4 connect to. Is there a way to update something on the master list and have that update the other individual lists?

The basic reason for this is, as we get an acknoledgement we update our own list. At the end of the day any one of us can go through the ones that came through by fax, etc. Is there a way that information can go back and forth between the 5 seperate files?

[TMS]

It's unlikely that you could update the "slaves" from the Master, given that they are likely to be in use and therefore open with full access.

However, it is possible that the slaves could open a read only copy of the Master on a regular, or ad-hoc basis, and copy the data from the Master.

But that's a VBA solution


Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TheNamelessPoet]

The "slaves" would be closed at that time, but this will work for now. Man I love Excel :-)

Thanks a ton for all th help!

[TMS]

You're welcome. Thanks for the rep.