Need Fixed Date Formula

Users submit new lines of info on our excel database. We want the dat
to self-generate when the adjacent fields are filled out, and we wan
the date to be fixed to the day the line was filled out.

=now() changes daily, so we cannot go back and see when the data wa
filled in.

We don't want to leave it a text field, because users often do not fil
the field in.

A formula would be greatly appreciated. Feel free to E-mai
wwoodall@riversideca.or

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

0
1/14/2004 8:09:34 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
305 Views

Similar Articles

[PageSpeed] 10

I don't think you can do this with formulas.  But you could use an event macro:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("b:IV")) Is Nothing Then Exit Sub
    
    On Error GoTo errHandler:

    Application.EnableEvents = False
    
    With Me.Cells(Target.Row, "A")
        If Application.CountA(.Offset(0, 1).Resize(1, Me.Columns.Count - 1)) _
           = 0 Then
            'they've emptied the cells
            .ClearContents
        Else
            If IsEmpty(.Value) Then
                .Value = Date
                .NumberFormat = "mm/dd/yyyy"
            End If
        End If
    End With
    
errHandler:
    Application.EnableEvents = True
    
End Sub

I used column A for my date cell.  Any change to column B to IV (255 columns)
will put the date in column A (if it's empty).  If it's filled in, it doesn't
damage the existing value.

rightclick on the worksheet's tab that should have this behavior.  Paste in the
code and back to excel to try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson also has some nice notes about workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm

"wwoodall <" wrote:
> 
> Users submit new lines of info on our excel database. We want the date
> to self-generate when the adjacent fields are filled out, and we want
> the date to be fixed to the day the line was filled out.
> 
> =now() changes daily, so we cannot go back and see when the data was
> filled in.
> 
> We don't want to leave it a text field, because users often do not fill
> the field in.
> 
> A formula would be greatly appreciated. Feel free to E-mail
> wwoodall@riversideca.org
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/15/2004 3:25:10 AM
Reply:

Similar Artilces:

What does ! mean as an operator within a formula
I am copying formula which has an exclamation mark prefixing a cell reference but I cannot determine what the operator calculates The ! separates the worksheet name from the cell reference. The more general form: =[Book2]Sheet1!$A$1 The workbook name is inside the brackets, followed by the worksheet name, the !, then the cell address. "Tam" wrote: > I am copying formula which has an exclamation mark prefixing a cell reference > but I cannot determine what the operator calculates Hello Tam. An exclamation mark follows a worksheet name in formula. Example: =Sales!...

Date conversion question
Am trying to understand why is that when I have a variable MaxDate (undeclared) and is has a value of 38697 and if I use =TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the format command didn't work either to convert MaxDate with the code =format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the correct output was 12/12/09. I am trying to understand this. Thanks, Chet Assuming you are using XL2003 or lower... if you click to Tools/Options = on the menu bar, th...

macro needs info on currently highlighted message in search folder
I created a search folder, "Messages Sent Or Received Prior To 2008) by using the Advanced Search and creating a query. The list of messages is shown, including just the lowest level folder name. I can navigate through the messages, and I'd like write a macro to show the full folder path of the currently selected (not opened) message, because I have many dozens of folders and they're not all named uniquely at the lowest level. Can this be done? Thanks tbone Show it where? Outlook version? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Program...

Help with what should be a simple formula..
Hi guys! I'm blanking out on how to write this simple formula, and honestly, not sure how to search the forum for help with this particular one. I'm trying to find profit margins basically. I want to break down profits into 1 Hr, 2 Hrs 8 hrs, etc.. for example, I the time it takes to produce item#1...say 10 Minutes.. In the 1 hr box want to figure out the profit based on 60 minutes of work.. My table looks like this.. A1=Item#1; B1=Cost to make Item; C1=Selling Price; D1=Time to make Item; E1=60 Minute profit; E2=120 Minute Profie.etc.tec.etc. A2=Item#2................................

Formula Help Please?
I'm trying to figure out the best formula to resolve the following: A B C Name Jan. Feb 1 Barb 8 3 2 Group 4 5 3 Ernest 6 7 4 Group 7 9 5 Heidi 9 4 6 Group 4 8 7 Total ____ ______ What we are trying to accomplish, is a formula that totals the values in column "B", however, those values which are "group" (B2, B4 & B6) should be multipl...

excell formula needed
Hi, In Excell, I'm trying to match a cell (eg H21,with a num value (eg 2530) to a column of figures (M34:M99) that has one match in it and produce a grading A, B, C, D from a column (N34:N99). Anyone have any ideas? Rob. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ =Vlookup(H21,$M$34:$N$99,2,false) Bernard "terrificRob" <terrificRob.uixfy@excelforum-nospam.com> wrote in message news:terrificRob.uixfy@excelforum-nospam.com... > Hi, > In Ex...

Nesting an additional formula in an IF statement
I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdeme...

Report Writer and effective dated tables
Hi, I am new to using report writer. I am trying to write a report that will just pull back the latest effecitve dated row by employee. A SQL statement that can do this would be something like: SELECT MAX(effective_date) FROM myTable WHERE effective_date <= Now(); I can not figure out how to do this in Report Writer. Any suggestions? Anyone know if this is possible? "badabing" wrote: > Hi, I am new to using report writer. I am trying to write a report that will > just pull back the latest effecitve dated row by employee. > > A SQL statement that can do thi...

I have MS Office XP Prof. & need System Office pack 2.0 or later
I just recently installed the Windows 7 Operating System on my computer. I use Microsoft Office XP Professional as my office software. While editing a document I received the following message: " The Office Assistant requires Microsoft Agent 2.0 or later. This product is available on the Office System Pack." Where can I get this? The link to request a hotfix is here: <http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=969168&kbln=en-us> The full article is here: <http://support.microsoft.com/kb/969168> hth Sabrina wrote: > &g...

Need help setting up fields on a liquor store import file.
What is the best way to set up the fields on the excel sheet (import) for a liquor store. Should I put the size of the bottle with the extended description or should i put the size in the sub description? I also have sku's (item lookup codes) for each child item but do not have one for the parent (a case of the product). How should I handle this? Do I make up my own lookup codes or should i just not use the parent child relationship and store everything as a child? What benefits does each one have? I always include the size in the primary description field. I always keep the d...

Due Date in Statement of accounts??
Hi , Can i print the sales statement of account showing the DUE DATE ? If yes, kindly please help me to create the field. Thanks in advance. ...

Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
I have this strang excel file. When I enter formular into it, it don't show thae value but when I enter anything else in the cell, that is visible. The formula is in the cell, its just not visible. On Apr 21, 7:41=A0am, Ayo <A...@discussions.microsoft.com> wrote: > I have this strang excel file. When I enter formular into it, it don't sh= ow > thae value but when I enter anything else in the cell, that is visible. T= he > formula is in the cell, its just not visible. Probably the sheet is protected and Hidden check box of Protection tab of Format Cell dial...

need help with tables so form okat- multiple equipment for 1 job
Here goes. I know this should be simple not sure why I can't see this right. Here is what I want to end up with not sure the best way to set up the tables to get what I want. and/or best way to create the form(from a query) or using form wizard pulling in tables that I need. I want to END up with a Form for all possible JOBS(cleaning jobs) with equipment needed and soaps needed for each job. There are many pieces of equipment for each job - mop, bucket, etc.(can't figure out how to get this in a form without listing with a comma) Have so far - don't thinks the tables a...

Can offset be used in this formula?
Can the offset be used in the below formula for the information in column "K"? In column L have the the following formula's L46 =MAX($I46*1000-$K$46*$J46,0) L47 =MAX($I47*1000-$K$46*$J47,0) .... L99 =MAX($I99*1000-$K$46*$J99,0) What I'd like to do is to copy the fomula into columns M, N, O, ... M47 =MAX($I47*1000-$K$47*$J47,0) M48 =MAX($I48*1000-$K$47*$J48,0) M49 =MAX($I49*1000-$K$47*$J49,0) .... N48 =MAX($I48*1000-$K$48*$J48,0) N49 =MAX($I49*1000-$K$48*$J49,0) .... O50 =MAX($I50*1000-$K$50*$J50,0) O51 =MAX($I51*1000-$K$50*$J51,0) .... How abou...

sort dates
hI My dates use the system dd/mm/year if i press the sort button.. it will sort by day->month->year I want to sort by year->month->day how do i do this?? thank -- Message posted from http://www.ExcelForum.com The best thing to do is to store your data as real Excel dates. Then they will always sort properly, regardless of which format you display the date in. As you have numbers, rather than dates, sorting isn't so easy. The choices are: 1. Create a Date field (=Date(year,month,day)) and sort on that. 2. Split out the day, month and year, then sort on the three separa...

Date in the query is always short date
I want to capture the LONG date from a query, so I create a parameter for the date (mm ddd yy) . In its properties, it's format is long date, BUT it always shows up the short date. WHY? Dates are stored as decimal number counting days from 12/31/1899 midnight with time as the decimal fraction of a day. Formats are just different ways to display the information. So you got to set the format. In design view of the query click the field of the grid that has the datetime field, right click, scroll down and select Properties. Click 'Format' and then in the pull down...

Have developed Visio App, need testers...
Hello, all, First, sorry about posting to several groups, but I'm hoping to reach a larger audience for this. Over the years, I have done a great deal of developing with VB, VBA, and several other languages, but mostly VBA and Office. One of my interests is logic and building digital circuits, so I found Visio quite useful - well, to a degree anyway. Some time ago, I searched high and low for a Visio solution with "live" circuits without much luck so I started building one myself. Needless to say, this turned out to be a bit more work than I anticipated, but now I have almost a ...

formula needed #2
Hi what is the formula to calculate the volume of a cask/barrel. I can find sites where I can put in the measurements but none display the actual formula. Thanks Tricia Hi Have a look here - at Q 19 ! http://tinyurl.com/ysw8e -- Andy. "Tricia" <tricialal2000@yahoo.co.uk> wrote in message news:e81vDyKREHA.1048@tk2msftngp13.phx.gbl... > Hi what is the formula to calculate the volume of a cask/barrel. I can find > sites where I can put in the measurements but none display the actual > formula. > Thanks Tricia > > Volume = ((radius * radius)* PI()) * he...

recurring dates
I need to be able to set set a deadline based on prior dates. These can go back years. I would like to set up a current 'next appointment, for instance ever 90 days, based on a date of arrival. For example, if date of arrival is 2/13/03, how do I make a formula that is effective now. In other words, the cell updates to a new deadline every ninety days, and stays current. Thanks a bunch. Maybe this which calculates the number of 90 day periods that have elapsed since the start date. =(FLOOR((A1-TODAY())/90,1)+1)*90+A1 Understand though that working with dates is tricky. There i...

Notes Connector not working need advice
I have recently installed Outlook 2003 and the Notes Connector for Outlook 2003. I cannot invoke the wizard to start the connection with my Notes server. Our company is on Ver. 5.5 of Notes. Others in my company are using the Notes Connector successfully so I am confident that the version is correct. Any ideas? I have XP Pro, Office 2003, and downloaded the proper Notes Connector for Outlook 2003. I have uninstalled Office completely, reinstalled it and reinstalled the connector without success. Outlook is completely oblivious that the connector is installed. Any help would be ...

Formula for adding totals from separate worksheets
I would like to do a summary totals page combining totals from 6 different worksheets. Can anyone assist with a formula for this? Thanks Worked it out - thanks. "TRM" wrote: > I would like to do a summary totals page combining totals from 6 different > worksheets. Can anyone assist with a formula for this? > > Thanks ...

Need help with Sub DeleteUnused Macro
Hi, I'm trying to run the subject macro from Debra Dalgliesh at: http://www.contextures.on.ca/xlfaqApp.html#Unused However, everytime I try and run the macro it gets hung up at this line of the macro ".Columns.Delete". I did the merged cell test and there were no merged cells. I'm running Microsoft Excel 2003 if that helps. Please advise, Steve Sub DeleteUnused() Dim myLastRow As Long Dim myLastCol As Long Dim wks As Worksheet Dim dummyRng As Range For Each wks In ActiveWorkbook.Worksheets With wks myLastRow = 0 myLastCol = 0 Set dummyRng = ...

Newbie needs info on a "new" start!
I will try to make this as simple as possible. I have an Excel worksheet for the year 2008 and among the data, there are closed and finished accounts. If I want to start a new datasheet for 2009 and only bring over the open accounts, how do I do that (in baby terms please). Thanks! Choose Data|Filter Filter on the Col with status with status as closed Press F5 key, click on Special as select 'Visible Cells' only Edit|Copy and Paste in the new sheet -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Terri"...

Modify general format to date format.
When importing from payroll, it gives me all dates formatted as general numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to convert these numbers into dates? If your Windows Regional Settings recognise a format of mddyyyy, you can apply the below formula and format the formula cell to date.. =--TEXT(A1,"00\-00\-0000") -- Jacob (MVP - Excel) "SherryS" wrote: > When importing from payroll, it gives me all dates formatted as general > numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to > convert the...

After IF formulae
HI, I have applied a IF statement to Colun H with results in Column I, (thanks to this group on showing me how) BUt now I would like to condense the results in Column I to a seperate sheet all together without all the spaces that are in the I after the IF statement was applied. Thanks for the extra help. Mike Checkout: http://www.cpearson.com/excel/noblanks.htm -- Gary''s Student - gsnu200841 "MikeR-Oz" wrote: > HI, > > I have applied a IF statement to Colun H with results in Column I, (thanks > to this group on showing me how) BUt now I would like...