Automatically enter date and time but only update once.

I have a workbook that contains 14 sheets. I have a sheet for each month 
followed by 2 sheets for information.

Each Month sheet has the following column headings associated from columns A 
through J:-

Owner; from date; number of days; to date, address, ID, month, input by; 
date; time.

I have to input data in columns A, B, C E, H, I and J.

Columns A and H are pick lists.

I have formulas in the following columns:-

Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)

Column F: =IF(ISBLANK(Enn),"",+Fnn+1)

I want column I to be populated AUTOMATICALLY (do not want to use the 
Control and semi-colon etc ) with the current date (dd mmm yy format) and 
column J to be populated with the current time (format hh:mm am/pm) only when 
column H is not blank. 

Once the date and time have been entered in columns I and J, I do not want 
it to be updated with a new time the next time someone goes into the work 
book or when the date changes the following day. It should only be populated 
to ‘blanks’ is there is no data in column H

Additionally, I do not have any experience of creating macro’s or VBA and 
therefore the information given needs to be plain!!

Any help greatly appreciated.

0
Mehta (6)
1/20/2005 11:29:02 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
710 Views

Similar Articles

[PageSpeed] 9

Take a look here:

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


In article <289F2570-A325-488D-BC8A-5D1F59AEB60D@microsoft.com>,
 "PM" <Pank Mehta@discussions.microsoft.com> wrote:

> I have a workbook that contains 14 sheets. I have a sheet for each month 
> followed by 2 sheets for information.
> 
> Each Month sheet has the following column headings associated from columns A 
> through J:-
> 
> Owner; from date; number of days; to date, address, ID, month, input by; 
> date; time.
> 
> I have to input data in columns A, B, C E, H, I and J.
> 
> Columns A and H are pick lists.
> 
> I have formulas in the following columns:-
> 
> Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)
> 
> Column F: =IF(ISBLANK(Enn),"",+Fnn+1)
> 
> I want column I to be populated AUTOMATICALLY (do not want to use the 
> Control and semi-colon etc ) with the current date (dd mmm yy format) and 
> column J to be populated with the current time (format hh:mm am/pm) only when 
> column H is not blank. 
> 
> Once the date and time have been entered in columns I and J, I do not want 
> it to be updated with a new time the next time someone goes into the work 
> book or when the date changes the following day. It should only be populated 
> to ‘blanks’ is there is no data in column H
> 
> Additionally, I do not have any experience of creating macro’s or VBA and 
> therefore the information given needs to be plain!!
0
jemcgimpsey (6723)
1/20/2005 1:09:31 PM
Many thanks Bob, it works a treat.

"PM" wrote:

> I have a workbook that contains 14 sheets. I have a sheet for each month 
> followed by 2 sheets for information.
> 
> Each Month sheet has the following column headings associated from columns A 
> through J:-
> 
> Owner; from date; number of days; to date, address, ID, month, input by; 
> date; time.
> 
> I have to input data in columns A, B, C E, H, I and J.
> 
> Columns A and H are pick lists.
> 
> I have formulas in the following columns:-
> 
> Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)
> 
> Column F: =IF(ISBLANK(Enn),"",+Fnn+1)
> 
> I want column I to be populated AUTOMATICALLY (do not want to use the 
> Control and semi-colon etc ) with the current date (dd mmm yy format) and 
> column J to be populated with the current time (format hh:mm am/pm) only when 
> column H is not blank. 
> 
> Once the date and time have been entered in columns I and J, I do not want 
> it to be updated with a new time the next time someone goes into the work 
> book or when the date changes the following day. It should only be populated 
> to ‘blanks’ is there is no data in column H
> 
> Additionally, I do not have any experience of creating macro’s or VBA and 
> therefore the information given needs to be plain!!
> 
> Any help greatly appreciated.
> 
0
PankMehta (14)
1/21/2005 7:47:02 AM
Reply:

Similar Artilces:

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Meeting updates #2
My users cannot update meetings created when they were on the old email server. I have noticed that the old string is still mapped to the meeting. e.g x400;c=us;a= ;p=Org name;o=exchagne;s=Lastname;g=firstname; Take a look at the following article: 275134 XADM: Cannot Reply to Messages That Are Sent from a User Account That http://support.microsoft.com/?id=275134 The same thing applies to meetings. How did you move them and what version(s) of Exchange? Thanks, Richard Roddy Microsoft Exchange Support This posting is provided "AS IS" with no warranties, and confers no ri...

Money Plus not Updating Quotes
For the past couple of days Money Plus has not been automatically updating stock quotes and manual quotes does not work either. I should add that this problem has been intermittent for the past couple of days. Any suggestions? In microsoft.public.money, D.Duck wrote: >For the past couple of days Money Plus has not been automatically updating >stock quotes and manual quotes does not work either. > >I should add that this problem has been intermittent for the past couple of >days. > >Any suggestions? > High server loading. Try again later. In microsoft.publi...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Time Clock Systems
Does anyone have a recommendation for a time clock system that integrates well with GP? On Oct 5, 10:20 am, kcd <k...@discussions.microsoft.com> wrote: > Does anyone have a recommendation for a time clock system that integrates > well with GP? We just implemented Time Matrix by Business Computers (www.business- computers.com) and are very happy with it. We implemented quickly the hardware wasn't propietary or complicated so we were able to source our own stuff. Troy I can speak highly of Unitime's time and attendance system. They are a relatively low cost solution t...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Online Price Updates
Somehow online price updating was turned on in my file. I have turned it off. However, when I go to delete the erroneous past-dated Online Prices for a particular stock, it appears they are initialling deleting, but when I return to the stock later, all the deleted online prices (from the past) are re-instated. It is as though none of the price deletions I performed have taken effect. How do I clean up these erroneous past online price updates? ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

Symbol Updating Only Every Few Days (if at all)
Using Money 2006, and have a symbol "VLO" that is only updating every few days. This stock was a duplicate (downloaded transaction created a new version of the same stock - my fault not clicking correct choice when asked). I've removed the symbol from the stock entry that was downloaded, renamed this entry to something bogus, deleting this renamed stock "from all accounts", then added the symbol back to the original VLO stock that I've been tracking for years. Now the stock just says "unch" in the portfolio view, and the price history is only updat...

Adding a word to the end of other words at the same time
I was wondering if there was a way to add a word to the end or beginning of multiple other words in Excel. Example; say I have these 3 words.... Alpha Beta Tera Now I want to add LLC to the end of each word but I want to change them all at the same time. Like Alpha LLC Beta LLC Tera LLC Is there a way to do that? Phil Its Excel 2003 try Sub addtexttoend() For Each c In Selection c.Value = c & " xxx" Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "phil" <ptukey@charter.net> wrote in message news:1125340358.873337.4240@g44g2000cwa.googlegroup...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Taste this important update
--cgzxgrfb Content-Type: multipart/related; boundary="uessxwmvq"; type="multipart/alternative" --uessxwmvq Content-Type: multipart/alternative; boundary="mweirjjojzzphfh" --mweirjjojzzphfh Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to m...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...

Updating External Links Excel 2000 vs 2002/2003
I have a situation where I'm using Excel 2000 with a workbook containing references to another workbook. When opening the first workbook & the second workbook is not available, you can say "no" to the update external links, and still see all values as they were when the first workbook was last closed. However, when the same workbook is opened in Excel 2002 or 2003, the external links specified only as a cell reference show the proper data (e.g, =wbkname!E1), but when they are Excel formulae (specifically a SUMIF), I'm getting a #VALUE! error in the pertinent cells Is th...

Entering More than 15 numbers
I'm trying to import numbers that are 25 characters in length. When I do this (or even if I enter over the 15 character limit) I get an overflow number. How do I enter or import numbers greater than 15 digits and have it display the entire number. I am using Office XP. precede with an apostrophe to get text, Excel's limit for numbers is 15 digits precision -- Regards, Peo Sjoblom "Mike" <anonymous@discussions.microsoft.com> wrote in message news:308101c428be$93500a10$a101280a@phx.gbl... > I'm trying to import numbers that are 25 characters in > lengt...

Outlook 2003
Hi Is it possible to order the deleted items by date of deletion in outlook 2003? Many thanks Lee Lee Atkinson <leeatkinsonlincs@hotmail.com> wrote: > Is it possible to order the deleted items by date of deletion in > outlook 2003? Use the Field Chooser to add the Modified time to the Deleted Items folder header bar (the one that says "From", "Subject", "Received", etc. and sort on that time. Deleting an item is a modification, so I would expect the modified date to be affected, giving you the most accurate value of the item's deleted time....

Automatically open Detail Entry Window
In SOP Transcation Entry, I'd like the Sales Customer Detail Entry window to open automatically after the customer ID is selected. Is this possible? How would I go about doing this with Modifier or VBA? Extender would come in very handy. You don't need to write a single code. Record a macro using GP macro and embed it on the SOP Entry window using Extender. "Elaine" wrote: > In SOP Transcation Entry, I'd like the Sales Customer Detail Entry window to > open automatically after the customer ID is selected. Is this possible? How > would I go about doi...