Static Date - Automatic Entry
Dec 12, 2011
I am attempting to create a formula which will detect of a cell is empty, and if it is empty enter the date by using TODAY(). I also dont want this date to change when opened after it has been entered. This is what I have been working with so far.
Code:
=IF(A1="",TODAY(),"")
Code:
=IF(G9>=TODAY(),A1=1,"")
I am trying to use the second formula to change the value in the cell "A1", but instead it just returns a true/false statement in the cell with this formula.
View 6 Replies
ADVERTISEMENT
Jul 16, 2008
I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.
Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.
View 14 Replies
View Related
Jan 12, 2012
How can I get an automatic time & date entry into cell B1 based on a alphanumeric entry in cell A1. The time and date must not re-calculate every time the workbook is opened.
View 4 Replies
View Related
Jan 7, 2014
I have a 12 month budget spreedsheet. Some expenses' are fixed every month. I would like those cells to be automatically filled with that fixed amount. Say on the 5th of every month a particular cell would have $50 automatically entered so i don't have to do it manually.
View 2 Replies
View Related
Jul 2, 2009
In the attached file I'd like to have a formula on cell B3 that would pick the number from the latest entry on "Actual" columns (column K, column M....). This should update the cell each month data is entered in respective month column.
View 4 Replies
View Related
May 10, 2007
know if there is any way of getting a cell produce an X (for example) when it is selected and blank when selected again? Basically a tick the bok response. I know i could use a drop down list but i was hoping for a quicker method as a lot of ticks accross a lot of cells are required.
James
View 9 Replies
View Related
Oct 2, 2008
So the title isn't really totally descriptive but it was all I could come up with. I have a work book that has 30 identical sets of 5 cells for users to enter in information. Some of my users will not need all 30 sets. I'm wondering if there is a way for the user to enter the number of cells they need and have excel then format so they have the desired number, instead of 30. Then have excel change all the instances of this number in formulas so that it matches the new number of cells. I hope I explained this well enough for someone to help.
View 9 Replies
View Related
Apr 12, 2006
I've been updating all the charts manually each time I update the entries in the worksheet which charts are created from in the same workbook. Is there any way (eg. by use of macros or VBA) that I can have all the charts updated automatically when data ranges are updated each time?
View 2 Replies
View Related
Jul 30, 2013
I have a userform, UserForm1, which lives in a spreadsheet called 'Data Entry.xls' There is nothing else on the spreadsheet itself, it's just for the use of a userform.
I would like the user to populate textboxs in UserForm1 but have that update cells in a separate spreadsheet 'Training.xls' in the same directory.
I have this code at the moment to find the next empty row and to input data into it, which is working perfectly to enter data to sheet1 in Data Entry.xls:
[CODE]Private Sub CommandButton1_Click()
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(eRow, 1) = TextBox1.Text
Cells(eRow, 2) = TextBox2.Text
Cells(eRow, 3) = TextBox6.Text
End Sub[CODE]
How can i modify it so it would do the same thing, i.e find the next empty row and then populate with what the user types in the textbox but in Sheet1 of 'Training.xls'
Would 'Training.xls' need to be open?
View 2 Replies
View Related
Jun 25, 2008
I have a user form that when a command button is clicked it enters the data from the from into coloums a,b,c,etc. I have code to do this but I want to add code to add a static date in coloum B based on if coloum A had data entered from the form. I need the date not to change to current date when the file is reopened. I am trying to elimate a date text box in the form. I have a link to the file http://www.box.net/shared/zdtsjv0qos
View 7 Replies
View Related
May 30, 2007
I want to create a monthly timesheet which contains 9 columns for
(Date, Day, Project no., Activity, Time In, Time Out, Total Hours, OT Hours, Remarks)
I have used IF Function to calculate Total Hours & OT hours automatically. Time IN & Time OUT, Project No., will be entered manually on daily basis.
Weekday function is used in the Day column to return the corresponding day of the date in the Date column.
Name of the Month and Year will be manually entered in the designated cells I3 and I4 respectively.
Now the solution I am looking for is, the dates should be automatically entered in the Date column (in cells A8 to A38) based on the Month & Year entered in cells I3 & I4. Dates of the corresponding month of the year should only be filled in. (If a month is not having 29, 30 or 31st day, the corresponding cells should be left blank. i.e. nothing should be displayed in the corresponding cells). I am looking for some sort of formula to enter in the cells of Date column (A8 to A38) achieve this. I have searched the forum and could not find anything which could at least give me an idea about the kind of function or formula to be used.
Attached here is the time sheet I am trying to create.
View 8 Replies
View Related
Jun 18, 2009
I have found threads that explain that if you want a to use the now() or today() function but make it static, you have to put a code in the worksheet relating to it.
I have read quiet a few threads, but i can't seem to make my code work.
I need to format column A to have the date format in it. I have a event procedure that puts Now() when run in Column A and I think i need to add the following code to the worksheet to make it static.
View 2 Replies
View Related
Feb 28, 2008
I have a rather simple question that I can't seem to find an answer to anywhere, even with a date stamp search on the board. I've found similar answers, but nothing specific to my problem.
I'm currently working on a department worksheet to track productivity. I have the formula in 2 cells to automatically display the current Sunday through Saturday dates when the worksheet is opened:=TODAY()-WEEKDAY(TODAY()-1) in E1 and=E1+6 in E2However, I need these dates to be static once the week is over for each worksheet and the workbook will contain a sheet for each week in the month (I'm not sure if that matters). I know this needs to be done in VBA, but I can't find how (if possible).
I'm new to this board and not a super Excel pro, so any help would be fantastic! Also, I'm working with the company's very old Excel97.
View 9 Replies
View Related
Aug 24, 2013
If I want date to be current and time constant how to do it for ex:
in A1 want display of 8/23/13 8:00 next day when I open excel A1 should display 8/24/13 8:00
so my date is dynamic but time is static.
View 6 Replies
View Related
Jan 9, 2010
Here is what I currently have to make the cell blank and highlighted yellow in the event the date in K1 equals the second day of any month
=IF(DAY(K$1)=2,"","N/A")
Conditional formatting is just =DAY(K$1)=2
What I need to do is apply these same conditions to the cell if the date in K1 is equal to 02/10/10 or 04/10/10 or 06/10/10 or 08/10/10 or 10/10/10 or 12/10/10
View 9 Replies
View Related
Nov 8, 2006
I am not sure whether this can happen, I want to capture time taken for different activities, without manually entering the details.I tried the formula "=Now()", but what happens is that at the end of the day the times are all the same because the live time is changing. Is there a way where the live time is captured and freezed, so that at the end of the day i have a time log sheet.
View 9 Replies
View Related
Dec 29, 2006
i am building a data base and every day, say, at 5pm a certain cell should contain a corresponding date. In my macro I simply wrote
Range("A1").Offset(i-1,0).Formula="=Today()"
I thought naiivly it would work, but it doesn't, as the next day the date will be changed correspondingly. So, the question is: how to record todays' date (Dec 29) so that this date stays in this cell forever? Only the date, not Excel Dates & Times
View 5 Replies
View Related
May 22, 2008
Is there a way for the current date to be displayed next to an item on my "Inventory" Sheet automatically once that particular item number is entered on the "Sales" Sheet, making sure that the date will not change/update (meaning TODAY and NOW are out)?
Also, this date would have to remain attached with that particular item number even if one or both of the sheets were sorted differently.
View 5 Replies
View Related
Aug 2, 2008
Here is the code I'm using
C5=IF(H5="X",NOW(),"")
C6=IF(H5="Y",NOW(),"")
C7=IF(H5="Z",NOW(),"")
Now H5 can be three different things, and all three different things are linked to three different cells. Once one cell gets populated with a date in this case is there a way to then have that cell stay populated even when H5 gets changed?
View 5 Replies
View Related
Jun 28, 2007
How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
View 5 Replies
View Related
Feb 11, 2006
What I am wanting to do is have excel insert a static date automatically. I know ctrl + : inserts date and ctrl shft + ; inserts time. I want excel to do this automatically. The now() and today() functions auto update the date. I dont want the date auto updated when the file is reopened after it is saved. The file is a template so I guess what I am really wanting is a automatic date/time stamp when the user saves the file so when it is emailed and reopened the date does not auto update.
View 6 Replies
View Related
Nov 29, 2012
what type of vba code I could use to populate a static date and time that I could plug into a formula. Basically what I need is:
IF A1<>"" then L1 shows the date and time of the entry. If A1="" then L1 remains blank. I don't want the date to change once it has been entered.
View 7 Replies
View Related
Nov 20, 2009
I'm trying to create vehicle maintenance inspection program for a mechanic at work. The trouble is the he's not fond of computer and I'm trying to make it as simple as possible. There's a debug in the line
View 2 Replies
View Related
Nov 22, 2010
I am trying to automatically insert a time into B and a date into A every time something is entered into C. I want the time and date to not change from the time it was posted. What formula will accomplish this?
I tried using if(C"",now(),"") while having Workbook Calculation set to automatic, but it would update every cell in column B every time I entered data into a new cell in column C.
View 8 Replies
View Related
Mar 12, 2008
I have a spreadsheet that has the date in one cell. I want the date to remain static unless I make a change. ie, when I first create the sheet I want a static date to go into this cell. Then I only want this date to change if any changes are made to the workbook (several sheets), I dont want the date to change everytime I open the spreadsheet. It must however update if I make any changes to particular cells on various sheets.
View 9 Replies
View Related
Jul 7, 2009
I needed a static date stamp to be entered into cell H3 when a "1" was entered into cell E3. This is the formula I found, it works fine too.
Formula for cell H3.....IF(E3="","",IF(H3="",NOW(),H3))
However, what I need to do is static date stamp the same cell, H3 but when a "1" is entered into EITHER E3 or F3
View 9 Replies
View Related
Aug 15, 2008
REQUEST 1
What I need is lets say I have 3 columns Name, Date & Time
Name field has a drop down menu having a list of names
Now what I need is - lets say from the dropdown menu someone selects a name - THEN at the same instance the Time & Date fields gets populated with the Date & Time of that update.
and that Date & Time shouldn’t change if someone selects the same or different name in the row below
Kindly assist in lay man terms as I am a novice to Excel
REQUEST 2
In this format what I need is like above , the start date & start time should update real time ONLY when a name is selected
and when the End date is selected the End time , Date Elapsed & Time Elapsed should Populate automatically.
However there should be NO effect if data is entered or changed in the Request Id or Issue Cells - basically the triggers should only be Name field & End date Field
Can you attach an example sheet as well please
Have enclosed an sample attachment
View 9 Replies
View Related
Sep 21, 2008
I've posted this question in another site http://en.allexperts.com/q/Excel-105...xExp_72253.htm , but i haven't gotten any answer. I'm attaching an example of the file to show an idea of what i'd like to do, please check first. As you can see, i have a Due Date and Priority columns. The Due Date column has a formula, that when i choose the Priority level, it calculates the Due Date from the current date and auto populates in the cell. The problem is, when i open the file the next day, and insert another Priority level, it updates everything even the previous Due Date entry. This is the formula i have in place for the Due Date:
=(TODAY())+SMALL(IF(WEEKDAY((TODAY())-1+ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0))))<6,ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0)))),(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0)))
Is there a way to make up a vb macro to be able to make the Due Date output static, without affecting the formula?
View 5 Replies
View Related
Feb 14, 2008
I belive i have a simple date stamp problem, but i do not know the correct VBA to edit the code posted below. I would like to have a datestamp placed in an adjacent cell based upon a particular value. For instance, If Cancel is entered into cell a2, then b2 would gave the date stamp. or if Started was entered into a2, then c2 would have the date stamp.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
View 3 Replies
View Related
Apr 10, 2008
I recently found on this site the macro to auto insert a static date in one column when an entry was made in the previous column.
What I have in my spreadsheet and what I would like to be able to do is if I select a particular Order Status from a drop down list, that it auto inserts the date into the respective columns.
Column F contains the various Order Status indicators ie Order Submitted, PO Raised, Delivered, and Invoice Received.
The date columns that I would like populated and which correspond to those status indicators (in order) are Column G, Column I, Column L, and Column K.
So if I pick Order Submitted, the status date should be auto populated in Column G. If I pick PO Raised, the status date should be auto populated in Column I etc.
View 5 Replies
View Related
