Static Date Function

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


Check Cell Has Date & Insert Current Static Date

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

View 7 Replies View Related

VBA To Give Static Output Of LEFT Function

Jul 10, 2009

What I would like to do is output the first 4 characters of a cell into another cell. eg Cell A1 has "1234 Sydney". If I just use the manual formula "=left(A1,4)" it would give me the correct output "1234" in say cell B2, but as a formula. As I need to automate this in a macro, how could you code this in VBA to output the String "1234" into cell B2(ie the actual output rather than a formula)?

View 2 Replies View Related

How To Make The Result Of A TODAY Function Static?

Mar 22, 2006

I have a problem with the today Function. It appears that it changes each day. But that isn't what I want! I'd like to have a funtion that puts the current Date in a field when Data is being added in the Row and then having this date static the next day. Here is what I had so far but I have no clue to make the date static:

=IF(C10>0;TODAY();IF(D10>0;TODAY();" "))

in this case it checks for information in field C10 and D10 and if there is information it will add a Date like 2006-03-22. But new day the field will change to 2006-03-23 and that is not what i want. I want it to stay the same when data is put in and the date is being presented.

View 12 Replies View Related

Making A Date Static

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

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.



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 View Related

Date Is Dynamic But Time Is Static

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

Formula For Static Date Formatting?

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


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

Add Static Date & Time To Cell

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

Record Todays Date As Static

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


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

Static Date Upon Submission Of Data

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

Convert NOW() To Static Date & Time

Aug 2, 2008

Here is the code I'm using


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

Static Date Stamp: Add A Date Stamp That Will Populate A Field When Text Is Entered Into Another Cell

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

Auto Insert Non Updating Static Date

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

How To Populate Static Date And Time Using Formula

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

Cannot Work Out The Debug With Copying Static Date

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

Automatically Posting Static Time And Date

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

Static Date Insert - Change When Modified

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

Static Date Stamp Input From Two Cells

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

Static Date & Time Corresponding To Cell Change

Aug 15, 2008


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


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

Volatile Formula Returning Date To Be Static

Sep 21, 2008

I've posted this question in another site , 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

Static Date Stamp Certains Cell Changes

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
End With
End If
End Sub

View 3 Replies View Related

Automatically Insert Static Date In Different Columns Of Same Row

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

Make Static Date Appear When Cell Has Text Entered?

Jan 19, 2014

Any way where is a list of cells (b3:b113) has text entered, or specific text entered, it will enter a static cell in the corresponding "d" column.

I know of CTRL + ; but i don't really know how to get it to work as a macro, can't find a forumla that enters the static date, and i don't want to make a macro to copy + paste special the date when it happens.

So all i should have to do is entered text in column b, then it will add a time stamp, when that text was entered, it doesn't have to update.

Like all i need is this, but instead of the "now()" function, it will have another function that doesn't automatically update?


View 2 Replies View Related

Subtract A Static Date And Time From The Current Date And Time

Oct 24, 2007

I have a column of values resulting from subtracting a static date and time from the current date and time.

This means it is constantly updating, which makes it impossible to sort.

All my work depends on sorting those values, though.

View 12 Replies View Related

DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)

Dec 8, 2009

I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:


but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

View 6 Replies View Related

Auto Populate Cell With Static Date As Another Cell Is Populated

Jan 25, 2008

I have used the following =IF(ISBLANK(Q6),"",(NOW())) & =IF(ISBLANK(Q6),"",(Today())),
but when the excel file is closed and opened the date changes to now or today. How can I make the date stay and not change?

View 4 Replies View Related

Automating Out Current Loan Period From Start Date And Today Date In IMPT Function?

Jun 12, 2014

Trying to automate the period part of the impt function

To calculate current value of loan i have the below formula below with the 3 being the current period


What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.

View 4 Replies View Related

VBA Keep Shape Static?

Mar 21, 2014

How do i Keep shape where it is no matter if i scroll up or down.

Sub KeepshapeStatic()
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheets("Sheet1")
Set shp = ws.Shapes("MyShape")
'How do i Keep shape where it is no matter if i scroll up or down
End Sub

View 6 Replies View Related

Static RND Inside For Loop

Feb 17, 2014

I am trying to create a For Loop where the RND from array column sptemp(i,2) is static throughout the entire loop when I pull the data out separately by columns to create a chart. The data works perfectly if I print it out on the excel spreadsheet via ctrl+shift+enter as an entire array but I am trying to graph the columns and the RND is updating and not syncing with the same random number, as sptemp(i,3) and sptemp(i,5) are both linked to sptemp(i,2) which is the RND. I am trying to graph column 3-5 on the Y-axis and column 1 on the X-axis, and column 3 and 5 should look identical after subtracting column 4.

View 1 Replies View Related

Copyrights 2005-15, All rights reserved