Question re: TODAY or NOW functions

Hi,
I use MS Office XP, and have set up a BOOK that has 2 worksheets, 
worksheet1 named 'checklist' contains a list of 60 establishments wh
send data to me on a monthly basis (rows), with a column for each mont
of the financial year. When i receive data for an establishment i tic
the corresponding cell in 'checklist' with a tick (capital 'P
webdings2).
I have recently set up worksheet2 as 'Date Received' and have copie
the table from checklist here. I want to make it so that as i enter 
tick in the checklist, 'date received' will automatically fill in th
date the data was input. I have tried using the following formula:

=IF('Check List'!E3="P",TODAY(),"")

this sort of works, but as a 'volatile' fucntion it will only ever sho
TODAYS date, not the date that the data was input!
I was wondering if anyone knows of a way to make an formula to show th
date that data was entered into a cell without it being a volatil
function.

I would be very grateful for any help with this

--
Message posted from http://www.ExcelForum.com

0
4/30/2004 10:12:14 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
360 Views

Similar Articles

[PageSpeed] 21

Why dont you just type in the date when the item is received o
worksheet1 in place of a tick

--
Message posted from http://www.ExcelForum.com

0
4/30/2004 12:23:59 PM
You would have to change the formula to a value or use a macro

=IF ucase(sheets("'Check List'").range("E3"))="P" then activecell=date


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Voutsy >" <<Voutsy.15itec@excelforum-nospam.com> wrote in message
news:Voutsy.15itec@excelforum-nospam.com...
> Hi,
> I use MS Office XP, and have set up a BOOK that has 2 worksheets,
> worksheet1 named 'checklist' contains a list of 60 establishments who
> send data to me on a monthly basis (rows), with a column for each month
> of the financial year. When i receive data for an establishment i tick
> the corresponding cell in 'checklist' with a tick (capital 'P'
> webdings2).
> I have recently set up worksheet2 as 'Date Received' and have copied
> the table from checklist here. I want to make it so that as i enter a
> tick in the checklist, 'date received' will automatically fill in the
> date the data was input. I have tried using the following formula:
>
> =IF('Check List'!E3="P",TODAY(),"")
>
> this sort of works, but as a 'volatile' fucntion it will only ever show
> TODAYS date, not the date that the data was input!
> I was wondering if anyone knows of a way to make an formula to show the
> date that data was entered into a cell without it being a volatile
> function.
>
> I would be very grateful for any help with this.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
4/30/2004 12:30:19 PM
Voutsy

CRTL + ;(semi-colon) will enter a hard date.  Instead of a tick, enter the
date.

If you truly want it automated you will have to use worksheet event code.

See J.E. McGimpsey's site for worksheet event code to set a date stamp.

http://www.mcgimpsey.com/excel/timestamp.html

Gord Dibben Excel MVP

On Fri, 30 Apr 2004 05:12:14 -0500, Voutsy
<<Voutsy.15itec@excelforum-nospam.com>> wrote:

>Hi,
>I use MS Office XP, and have set up a BOOK that has 2 worksheets, 
>worksheet1 named 'checklist' contains a list of 60 establishments who
>send data to me on a monthly basis (rows), with a column for each month
>of the financial year. When i receive data for an establishment i tick
>the corresponding cell in 'checklist' with a tick (capital 'P'
>webdings2).
>I have recently set up worksheet2 as 'Date Received' and have copied
>the table from checklist here. I want to make it so that as i enter a
>tick in the checklist, 'date received' will automatically fill in the
>date the data was input. I have tried using the following formula:
>
>=IF('Check List'!E3="P",TODAY(),"")
>
>this sort of works, but as a 'volatile' fucntion it will only ever show
>TODAYS date, not the date that the data was input!
>I was wondering if anyone knows of a way to make an formula to show the
>date that data was entered into a cell without it being a volatile
>function.
>
>I would be very grateful for any help with this.
>
>
>---
>Message posted from http://www.ExcelForum.com/

0
Gord
4/30/2004 4:23:35 PM
Reply:

Similar Artilces:

Backup question
I've moved to a new pc and for some reason I thought that when you backed up, a new file was created. Am I right? At the moment, it's just backing up to the backup file. So I've just just my working file and my backup file. Please could you clarify this? Thanks again. In microsoft.public.money, abc wrote: >I've moved to a new pc and for some reason I thought that when you >backed up, a new file was created. Am I right? At the moment, it's >just backing up to the backup file. So I've just just my working file >and my backup file. P...

Question about Xml Schemas "qualified" and "unqualified"
In the textbook, there is a sentence that "Default XML namespaces(xmlns="...") helps a lot, but can also create problems, as a side effect of the rules for automatic qualification. How to understand "automatic qualification" here? Could you please give me an example? Secondly, unless otherwise specified, a schema prescribes that loal elements and attributes must be "unqualified". What does "unqualified" mean? Could you please giv eme an example? Finnaly, could you please tell me what's purpose of using these two things? Thanks a lot! ...

Today's date in Chart
I created a dynamic gantt chart in Excel 2007 which changes based on the current date "=TODAY()". Is there a way to have the current date automatically appear somewhere in the produced bar chart as a "legend"? Thanks. I tested this in xl2003, so you'll have to retest... I added a textbox from the Drawing toolbar into my chart. With the textbox selected, I typed: =a1 in the formula bar Then I typed: =today() in A1 And it seemed to work ok. I changed the numberformat to other date formats and that was reflected in my textbox in the chart, too...

Problem cut-n-paste function
I'm having a bizarre problem cut-n-pasting a very simple calculation (ie = A1 � B1) in C1 down the column (ie C2 to Cn). When I cut-n-paste the function in C1, value that displays is the same as the source cell. If I click on the target cell, the correct cells are highlighted and if I hit the enter button, the formula calculates as expected. What is going on? What do I have to do to force the formula to auto calculate? Turn on Automatic Calculation <g>. You do that at Tools/Options/Calculation. On 27 Sep 2004 18:56:34 -0700, jsausten@hotmail.com (Stephen Miller) wrote: >I'm...

a question and a question
What is the correct name of the type of selection box used in "customizing Word", for example.... you select a word or operation from a list on the left pane and move it over to the right pane. Is there a template to build one of these? thanx You will have to explain more in detail what you are trying to do. -- Stefan Blom Microsoft Word MVP "cliffordjf" <cliffordjf@discussions.microsoft.com> wrote in message news:9856CCA7-8A8E-440C-A0D4-76EE4FA644E2@microsoft.com... > What is the correct name of the type of selection box used in "c...

Nested functions in conditional formatting formulae
I am trying to format a range of cells as a function of cell value compared with other cells. My attempts have failed so far. For e.g.: =OR(ADDRESS(ROW(),COLUMN())=$B$18,ADDRESS(ROW(),COLUMN())=$B$17) =OR(INDIRECT(CONCATENATE("R",(ROW()+1),"C",COLUMN()),FALSE)=B17) I do not wish to write VB code. Thanks in advance for any ideas. Maybe you could post what you are trying to achive it's hard to guess what you are trying to do -- Regards, Peo Sjoblom "Joseph" <Joseph@discussions.microsoft.com> wrote in message news:3331CF9E-E84E-44D0-8186-6F9E19DEE2...

Re: Ordering a replacement disc.
I need a replacement disc for Publisher 2000 (disc 1)which cracked and is unusable. Does anyone know who to contact to order a replacement? "Jeanne Jelinek" wrote: > I need a replacement disc for Publisher 2000 (disc 1)which > cracked and is unusable. Does anyone know who to contact > to order a replacement? ===================================== To obtain replacement CD-ROMs or manuals, contact the Microsoft Replacement and Supplemental Parts Department at (800) 360-7561. When you call for a replacement CD, you may be asked to fax a copy of the store receipt to the Mic...

Is there anything strange about NZ function?
I can run my query with NZ function from within Access however cannot run it via OLEDB connection even though its using Jet DB engine. Here is my query, I tried changing it to not use NZ but nothing seems to work the same. SELECT ID, T1 & IIF(NZ(T2,'')='','','--') & T2 & IIF(NZ(T3,'')='','','--') & T3 AS KWDesc FROM tblKWFlat ORDER BY T1, T2, T3; Basically I need three columns if not null to be separated by '--' e.g. abc abc--def abc--def--ghi Any ideas? Nz is a VBA function, and probably isn't ...

IF AND question
Hi there, I need a function that can provide one of three answers: 1 2 1 1... 3 3 4 4... ? ? ? ?... If A1 = 1 and A2=3, answer 106; but if A1=1 and A2 = 4, answer 104; but if A1=2, regardless of A2, answer 95. The next function for column B is the same, except the answer is dependent on the value delivered from the column A function. E.g. A3 + 6 or A3 +4 or A3-5 etc. Any help would be most appreciated. -- ***** Many thanks Gamq Use the below formula for your first query. =IF(A1="","",IF(A1=2,95,IF(AND(A1=1,A2=3),106,IF(AND(A1=1,A2=4),104)))) ...

IE8 privacy question
I am running XP-Pro SP3 and considering upgrading from IE7 to IE8. I have read all the feature and benefit articles from MS, but have one question that remains unanswered. In IE8 there is mention of being able to restore previously viewed websites or something to that effect. I think this is on a drop down menu somewhere. 1) Does In-private viewing prevent this? 2) Does manually deleting your complete browsing history clear this? 3) Is there a registry entry that can prevent this action? 4) Is there a group policy change that can prevent this action? In essence for privacy pur...

Question about Paste Special
Hi, I have a problem sometimes with the paste special options when goin from one excel workbook to another. For example, sometimes when I cop data from one workbook and then paste special into another, I get th options that include: All, formulas, values, formats, has the option t transpose the data and paste link among other options. And the sometimes I try and paste data to another workbook and I end up th paste special options: Bitmap Image Object, picture, bitmap, and I als lose the ability to paste link. Well you can do it, but it puts it i as an object. What I want is the first past...

Microsoft Query question
I am trying to use Microsoft Query to get data from my SQL 2000 databases. The problem is that I have a few columns with names that conflict with keywords (like Identity). (Yes. I kow that keywords should be avoided but it is done and cannot be changed.) This is causing a keyword syntax error within the Query application. Oddly enough, even if I do not select the column the error still appears. And when you look at all the columns available, the keyword named column shows up in boldface. When I am doing queries in other programs I put square ("[ ]") brackets around column and table...

Question
Why did the chicken cross the road? -- Dr. Stephen Hopkins, MD "Dr. Stephen Hopkins, MD" <DrStephenHopkinsMD@discussions.microsoft.com> wrote in message news:B8434E21-DDA3-44D7-B39B-CD5A8C33A7BD@microsoft.com... > Why did the chicken cross the road? > -- > Dr. Stephen Hopkins, MD To collect her email, why else? Dr? You should be ashamed to put such a title on an idiotic off topic post !! -- Regards Steve. MS-MVP. MAIL. [DTS] UK. http://www.getsafeonline.org/ mac;1266180 Wrote: > "Dr. Stephen Hopkins, MD" <DrSte...

OFFSET function?
hello! I want cells A1,A2,A3 to return the values in cells B12, B24, B36 (i.e. every 12th month in my case). Something to do with OFFSET I guess but I can't get it to work. as ever, thanks to all those out there who help... Jose Hi For A1 =OFFSET($A$1,ROW()*12-1,1) and copy down -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "Jose Mourinho" <JoseMourinho@discussions.microsoft.com> wrote in message news:1C0D4FA7-EE08-4DF6-A632-16F066EEB52E@microsoft.com... > hello! > > I want cells A1,A2,A3 to return the values in cells B12, B24, B3...

OWA Question #16
Hello All: Quick Question regarding OWA. We are about to finalize migration from 5.5 to 2003. We have an existing web presence already in DNS a www.mydomain.com. and running on existing web servers. With 5.5 natrually, to access OWA, it was www.mydomain.com/exchange. This will not be possible now since OWA runs off of the Exchange server instead of relying on our web server under 5.5. What's the easiest way to overcome this. Thanks If you can afford the extra license, you'll probably want to run a front-end server so no one is connecting directly to the Exchange database s...

VBA//Oracle Interfacing Question
All, I have been able to correct to my database but I have one question question: Within objSession I want to list all available tables and all available views. How exactly is this done? thank you. Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("", "User/Pass", 0) On Nov 16, 12:05=A0pm, jason <jason.mell...@gmail.com> wrote: > All, > > I have been able to correct to my database but I have one question > question: > > Within objSession I want to list all avail...

Text Box Question
Greetings, I have been using Visio for years, however, something has happened and I can't figure out how to undo it. Basically up until today when I added a text box and entered text, the text went horizontal as it was typed. Today when I add a text box and enter text, the text goes vertical. How do I get back to the old behavior? TIA using text container shape adjusting tool (text block tool), make the text container shape wider horizontally. Have you used Asian text font recently?...check help for "vertical text" "Ray Batig" wrote: > Greetings, > &g...

401K questions
I just started a 401K at work and I'm wondering how to deal with it in Money. I've got my paycheck split into different categories currently and now I have to put the 401K amount into a category as well. I didn't see one specifically for this. What do other people use? Should I start using the 401K Manager? I haven't gone through it yet I'm just wondering if it works well and how much benefit I'll get from it. Thanks in advance! Mike You will want to create an investment account for the 401(k) and TRANSFER your contributions from your checking account to the...

If / Then Question
How would I do this: IF A1="N" then I need A2 and A3 to="N/A -- pkniven ----------------------------------------------------------------------- pknivens's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2767 View this thread: http://www.excelforum.com/showthread.php?threadid=47531 pknivens Wrote: > How would I do this: > > IF A1="N" then I need A2 and A3 to="N/A" Hi pknivens In A2 and A3 use this formula > =IF(A1="N","N/A",""), this will return blank if A1 is not N, if you want it...

Fibonacci function/command in excel?...tia sal
Fibonacci function/command in excel?...tia sal Greetings all, Is thier a Fibonacci function or command built into excel? TIA Please don't post the same message in different newsgroups. It only tends to fragment your answers, and to potentially waste the time of someone answering a post that's already been answered. For tips on using these groups effectively, see http://cpearson.com/excel/newposte.htm In article <IVjpe.62542$lQ3.45092@bignews5.bellsouth.net>, <sal@spp.net> wrote: > Fibonacci function/command in excel?...tia sal > > Greetings all, &...

ROWCOUNT question
Hi All, The below is a UDF that returns the ROWCOUNT for a table. I need to know that value for WHILE loop. How do I take the Returned value from the UDF and load it intio a variable in the SP where it was called from? Or maybe there is another way. ALTER FUNCTION [dbo].[RecCount] ( @TableName CHAR(15) ) RETURNS TABLE AS RETURN ( SELECT Rows FROM sysindexes WHERE id = OBJECT_ID(@TableName) AND indid < 2 ) Thanks, Eric Eric S (xxx_noSpam@Hotmail.com) writes: > The below is a UDF that returns the ROWCOUNT for a table. I need to know > that va...

Exmerge Question #7
When I run Exmerge on a mailbox on my 2003 server to export to a PST, it will delete all emails after the copy. I thought EXmerge would "copy" not delete. is this the default? If so how can I just have it do a copy out to the PST and leave the mailbox alone. thanks Rick in "options", make sure "archive data to target store" is not selected on the "Import procedure" tab... -- Susan Conkey [MVP] "Rick" <drummer10980@gmail.com> wrote in message news:1165509540.777142.38260@16g2000cwy.googlegroups.com... > When I run Exmerge on ...

second (now())
Hey, I need a realtime time (seconds ) function in a cell. second(now()) is not a realtime function. I need to push F9 to have the real time calculated. Do I need vba for this and how? Greets, R. Langdon Try this site for a timer: http://www.cpearson.com/excel/ontime.htm Hope this helps. Pete ...

question about "delete"
I have a pointer: MyWindowClass *p = NULL; p = new MyClass(...); .... delete p; After delete p, does p equal NULL(it is in C++ standard?)? How to decide if p has been deleted? The reason I asked this question is that in my project, there are many code/files use the pointer which I need to determine is it is deleted? Can I use: if(p != NULL) delete p; I guess somewhere p has been deleted, but p still not NULL(possible?), the above code might cause problem. Hi Kathy, I typically just set p to NULL when I delete it: delete p; p = NULL; Then you can check it in other places and ...

Re: capitalizing appointments in Calendar in Outlook 2007
Date: Tue, 02 Mar 2010 14:04:22 -0800 From: "Leonid S. Knyshov // SBS Expert" <LeonidSKnyshovSBSExpert@discussions.microsoft.com> On 3/2/2010 1:26 PM, test wrote: >> I have twenty different activities for each month which are present on the Outlook 2007 calendar. While entering the activities, I entered them in Sentence Case like "Meeting with Group A about Topic B". \\line\\line Now, is there a way I can capitalize the activites so that it looks like "MEETING WITH GROUP A ABOUT TOPIC B" in the Calendar without retyping it or copying and pasting t...