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
449 Views

Similar Articles

[PageSpeed] 25

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:

question about cals
Hi, Our network is a 2003 network at the moment. I want to make a begin with the upgrade to 2008 servers. The first server i want to upgrade is our fileserver. I know I need 2008 cal's for licensing requirements. What happens if i upgrade this server today but will have the cals 2 weeks later? Can users still connect to this fileserver? Ramon ...

Word 2007 Reviewer question
Hi there Sorry, this is a duplicate question to the one I posted in the Office area before, but I put my original question there by mistake. I have a problem with someone I support where she is adding comments into a word doc as a reviewer ... When she adds the comment in word flags it as Reviewer and marks the comment as R1 R2 etc. But when she saves the document when she is done Word changes it to Author and marks the comments as A1 A2 etc ... this of course makes her comments hard to find. Do you have any suggestions where I might look to fix this? many thanks Chris ...

Advanced chart question
Hi, I have an interesting question on charting, hope someone can help me out with it. For a performance analysis I am showing both the performance of a portfolio (line) and its corresponding benchmark (bar). In order to make the out- or under performance more visible I would like to give to "balls" in the line a red color in case of under performance and a green color in case of out performance. Obviously you can do this manual but I have over twenty graphs with 20 points;- (........... Any suggestions if this can be set automatic?? many thanks! Rgds Robert Hi See if the arti...

Download from Financial Inst. Question???
I am thinking of moving from Wachovia to H&R Block Financial. Has anyone used H&R Block in Money??? Frank ...

MOVE or COPY not Functioned..
I have two excel worksheets in one workbook. I tried to move one worksheet, to another opened workbook. usually we find another workbook in the menu when we tried to move.. instead i'm seeing only this workbook and new workbook options.. how to enable to see another workbook...? Sounds like you have your two workbooks open in separate instances of Excel, which will not see each other. Close one instance and open both workbooks in a single instance of Excel. Gord Dibben MS Excel MVP On Mon, 15 Feb 2010 09:59:17 -0800 (PST), AxisNovice <gsvpkd2grps@yahoo.com> wr...

Wrong name now...
I bought a used Acer Netbook. It is terrific. It has XP on it, that I may leave or may change to Vista. I don't want to lose the applications, so I'll probably leave it as is. My problem is that the prior owner's name - "John Smith" - is built into Windows somewhere. I have changed the name of the user to Paul, but his name comes up frequently. How can I change it to my name? TIA, Paul The name is set as the RegisteredOwner under this key in the Registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion steve "Paul Hobe...

cashflow in your bank account NOW!!!!!
i think u should try these websites! ;) go to this url: a) http://offto.net/forexkiss1/ b) http://offto.net/5technic1/ c) http://offto.net/50weeksforex1/ d) http://offto.net/makemoneyfirst1/ e) http://offto.net/cashsecret1/ f) http://offto.net/millionairesecret1/ g) http://offto.net/luxuryhomes/ h) http://offto.net/forextrader1/ well, these are just my suggestions.. i dont get any commissions for promoting these websites to you. good luck! ;) ...

Money 2002 question
I'm trying to restore my files in Money 2002,however the program asks for a password for this to happen. Anyone know how I can get my files back up and running or get past the password window? As far as I know, I never did use a password to backup but, now it is asking?? Any help would be greatly appreciated! Did you ever open these files in M03 or M04? If so, these FAQs apply: --- Q): I tried the (M03) trial version and didn't like it. Now (M2K) wants a password to open my file. What gives? A): Didn't you read the screens that popped up when it upgraded your file to ...

Start Earning Just 15 Minutes From Now...
Hey If you're still struggling to get those first few dollars online, then this could be the big break you've been waiting for... With this proven system you can literally start generating totally passive income in just 15 minutes from now... Everything is done for you... => http://cbpirate.com/main/brazilian It's called Clickbank Pirate, and when I say simple, I mean it... Here a few things you DON'T have to do once you have Clickbank Pirate: * You don't have to sell anything... You read that right - with this system all you have to do is give away valuable free ...

stacked column question
I have the following data: A B C D E Jan 09 10 10 100 90 Feb 09 15 14 90 90 Mar 09 15 13 95 90 i want to create a stacked column chart where columns B and D are stacked together, and columns C and E are stacked along side the B/D column, for each month. How do I do that? ...

User Defined Function returning #Value!
I have a function that I created. When I test it in the Intermediate Window, ? Kountifs("Registered Nurse"), it returns a 12 which is correct. I want to be able to use this function a my datasheet. I have included basically the same function =Kountifs("Registered Nurse"). But, ont the datasheet I receive a #Value! rather than the 12. Does anyone have ideas why? Why dont you post the UDF.. If this post helps click Yes --------------- Jacob Skaria "DogLover" wrote: > I have a function that I created. When I test it in the Intermed...

excel 2007 localized function names -> english names
This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C89E65.327C1FA0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, Is there any way how to change localized function names in excel 2007 to = english ? It's silly, that MS localized also this. Thanks for replies ------=_NextPart_000_0025_01C89E65.327C1FA0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> &l...

PST synching question?
Currently I put my pst file (desktop) on a usb disk and then load it to my laptop and work on it. Later I reverse the process, like at the end of the day when I want to put it back on the desktop with updates. Question: Is this dangerous/risky or would it be better to use a synching program to sychronize pst changes from laptop to/from desktop? I've had no problems yet but I am wondering am I taking any risk, and since I have seen a lot of synching programs (indicating that pst download/upload is not too popular) maybe I am doing it wrong. Thanks Kita <kita@kit.com> wrote in...

Newbee: Compiler says to me that public function pointer is protected
I'm studying how to create simple dlls. Following the sample I created one simple dl (code below): ..h file: #ifdef DLLFUNC_EXPORTS #define DLLFUNC_API __declspec(dllexport) #else #define DLLFUNC_API __declspec(dllimport) #endif DLLFUNC_API double Fatorial(double nNumber); the .def file: LIBRARY DLLFunc EXPORTS Fatorial @ 1 the .cpp file: DLLFUNC_API double Fatorial(double nNumber) { double Result = 1; for(; nNumero > 1; nNumber--) { Result *= nNumber; } return Result; } Following I've created a simple MFC dialog app to test the dll. In the testDLLFuncApp::InitIn...

Upgraded to version 10, now most AP batches get error in GL rate t
Posting in 1 company and some, but not all, MC batches stop at the GL and errors are "The rate type ID is missing" and "The exchange table ID is missing." This company uses project, our other company does not (haven't had that error at all). Checklinks have already been run on MC seutp, there is no issue with our accounts not being assigned to currency, only have 1 rate type setup for the currency, NO issues in Dynamics 9. New error in version 10. Have applied the hotfix to SP 3 that concerns some issue with cheques applied to project invoices with t...

Counting question #2
I have a range of cells that I want to 'count' if the number is greater than 0 but less than 6. The cell # is F33 where I want the answer. The range is: Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29. What formula would I use? I've tried several but I keep getting error answers. hi, =count(Q13:W13,Q17:W17,Q21:W17,Q25:W25,Q29:W29) >-----Original Message----- >I have a range of cells that I want to 'count' if the number is greater than >0 but less than 6. The cell # is F33 where I want the answer. The range is: >Q13:W13; Q17:W17; Q21:W17; Q25:W25...

Going From Excel 97 To Excel 2007 Questions From Sr. Citizen, Please
Hello, Am in my 70's now, and a lot of this is becoming a bit harder. May I ask, please: a. Have been using Excel 97 to simply plot out my blood pressure for my MD over the years. Old Excel, but did everything I needed, and was sure easy to make plots from. Just installed Office 97 with the new Excel. Big mistake, I think. My old graphs in Excel 97 (it's been so long now I don't remember all the things I did to create it) has a "trend line" thru the points. Guess it's a simple linear rms line fit. The new Excel, when importing the old file, has the points and...

SUMPRODUCT with date range question
Hi all, I'm having trouble creating a formula as follows: Column A: either Y or N Coumn B: a forecasted date Column C: $$ amount of transaction What I would like to do is create a report that shows the sum of column C for anything where column A is "Y" and the date in column B is today's date or up to 90 days after today's date (Today +90)... I'm having problems with the date criteria, and would appreciate your help! Thanks try something like this =SUMPRODUCT((A7:A10="Y")*(B7:B10>=TODAY())*(B7:B10<=TODAY()+90)*C7:C10)- Don Guillett SalesAid So...

Group By
I have a view which uses group by - containing 2 aggragate functions, Sum() and Count(). What I want to do is include in my Select portion of my view , a column that I do not want to be included in the Group by. Is there a simple way to do this? Example: Select A.Field1,B.Field2,A.Field3,A.Field4,A.Field5,Sum(Field2),Count(*) From tablename A Left Outer Join Tablename2 B on B.Rec_ID = A.Rec_ID Group by A.Field1, B.Field2 A.Field3 A.Field4 (Note: In this case A.Field5 is EXCLUDED in the Group by) Your help is greatly appreciated. -Ray Field2 is in the ...

Question about how to use range in VBA
Hello, I am trying to cut and paste one range from one sheet to the same range in a different sheet. I used the following code: Range(Cells(startrow, 1), Cells(endrow, 12)).Select Selection.Copy Worksheets("SUMM").Activate Range(Cells(startrow, 1), Cells(endrow, 12)).Select --- ERROR HERE ActiveSheet.Paste This code gives me an error where indicated: Application defined or Object defined error Notice that I use the exact same line of code three lines earlier and I get no error. If I change my code to read as Range(Cells(startrow, 1), Cells(endrow, 12)).Sel...

Serialized Kits or Assembly Items? A Functionality Question
Hi there! We are new to the RMS world, and seem to be stumbling on a key concept. We want to sell a computer system, built from components in inventory. Naturally we want to record serial numbers for each item whether it be sold individually or as part of a system build. Additionally we want the computer itself to have a serial number. I don't see a way to create a kit that contains serialized items, and when we make an assembly item the price comes out wrong. (The computer costs less than the retail price of all the components.) Can anyone help out with a strategy or give insight...

Detect & Repair Question Please
I am running XPPro & Office 2000 Small Business edition. Every so often Outlook refuses to start & I must run "detect & repair" and that fixes it okay (I still don't know what's causing this however). My question is: when I chose "detect & repair" I an asked to put in my original installation disc. This is a pain. Surely whatever detect & repair is looking for can be installed on my hard drive so I don't have to keep going back to the install disc. Help please. Thanks in advance... Bob ...

We're Talking Basic (I think)
I am creating a price list. BIG. I want to be able to go into the price list and apply a percentage increase throughout every cell that has a dollar value. :rolleyes: Change the prices by th percent desired, drag and drop into a layout, print and send t customers.:confused: I have no Excel experience to speak of and no "User handbook"). :eek: What say? Anyone who can help? :confused: Thank you. G -- gwhayduk ----------------------------------------------------------------------- gwhayduke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3688 Vie...

paste info from excel into another spreadsheet & have todays date.
i would like to transpfer data from an excel spreadsheet into antoher document, but when i paste it I would like the current date to automatically transfer next to it. hi, i am not sure that can be done with a simple paste command you can use the keyboard shortcut Ctrl + L to insert the date after the paste. >-----Original Message----- >i would like to transpfer data from an excel spreadsheet into antoher >document, but when i paste it I would like the current date to automatically >transfer next to it. >. > Hi not sure that Ctrl & L is the standard shortcut fo...

queue process question
Hi, When setting up a queue in MS CRM 4.0 and pointing to an email address and selecting to pull all emails, does it read the Inbox folder of that email address being selected or does it read the all the folders? thanks Hi John, It reads from the Inbox only. Also note that unlike the forward mailbox, messages that are processed is not automatically removed from the inbox. This means that should you delete the email activity from CRM, with the next iteration of the email router it will be tracked again. Hope this helps -- Microsoft Certified Technology Specialist - Dynamics &qu...