Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Static Date Function

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Check Cell Has Date & Insert Current Static Date
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 Replies!   View Related
How To Make The Result Of A TODAY Function Static?
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 Replies!   View Related
VBA To Give Static Output Of LEFT Function
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 Replies!   View Related
Making A Date Static
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 Replies!   View Related
Static Date Stamp: Add A Date Stamp That Will Populate A Field When Text Is Entered Into Another Cell
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 Replies!   View Related
Formula For Static Date Formatting?
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 Replies!   View Related
Record Todays Date As Static
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 Replies!   View Related
Static Date Upon Submission Of Data
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 Replies!   View Related
Static Date Insert - Change When Modified
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 Replies!   View Related
Static Date Stamp Input From Two Cells
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 Replies!   View Related
Cannot Work Out The Debug With Copying Static Date
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 Replies!   View Related
Convert NOW() To Static Date & Time
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 Replies!   View Related
Static Date Stamp Certains Cell Changes
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 Replies!   View Related
Volatile Formula Returning Date To Be Static
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 Replies!   View Related
Static Date & Time Corresponding To Cell Change

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 Replies!   View Related
Add Static Date & Time To Cell
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 Replies!   View Related
Automatically Insert Static Date In Different Columns Of Same Row
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 Replies!   View Related
Subtract A Static Date And Time From The Current Date And Time
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 Replies!   View Related
DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
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 Replies!   View Related
Auto Populate Cell With Static Date As Another Cell Is Populated
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 Replies!   View Related
Static Collection Of Textboxes
I have a routine that creates a collection of textboxes to simplify adjusting their positions: ...

View Replies!   View Related
Auto Static Time
When a value is placed in a cell (Mr Brown) I would like a static time to automatically appear in the next cell.

I have never written macros or used the VBA.

Can someone give me simple, step by step instructions on how to do it.

View Replies!   View Related
Possible To Make Sheets Static?
what im creating is a live odds program for NFL football... and in my workbook I have ALL the teams as individual sheets.

The problem im having is say im looking at the calculations for Pittsburgh on the pittsburgh sheet, and my macro decides to update.

What it does is pulls me away from the pittsburgh sheet and goes through all the updates for all the teams on all the sheets. By the time its updated all the odds for all the sheets and running and highlighting with the other macros I have to click back to Pittsburgh and view again for like 20 seconds.. and away she goes again updating the sheets.

Is there any substitute for "select" in the programming where it will not select the sheet.. but just update it "behind the scenes" ?? making the page static and remaining viewable while it runs all the processes in the background??

( yep im pretty new at all this, but i have a great method for calculating games and winners each week, but probably better without looking for 20 seconds waiting, clicking back to the sheet and looking for another 20 seconds .. etc etc .)

View Replies!   View Related
Making A Formula Static
I have 2 sheets in a work book. In the first sheet in A27 I have a value. In the secend sheet I have a formula "=SHEET1!$A$27".

Problem is when I insert a new row in sheet 1 the formula in sheet 2 changes to "=SHEET1$A$28" and I want the formula in sheet 2 to stay the same that is, always =SHEET1!$A$27.

View Replies!   View Related
Change Formula To Static Value In VB
I have the following code that places a formula into the cell.

View Replies!   View Related
Worksheet Calculate() And Static Variables
I have a spreadsheet with a real-time data link in cell A1 and I use Worksheet Calculate() to paste the new value, on any update,below cell A1. Once I have 10 values from A2 down to A11, I over-write the first one, so that I have the 10 most recent. From this I calculate the moving average.

Anyway, I would like to be able to do this without the pasting bit ie storing the most recent ten data points in a variable say x(i) and just averaging x(1) to x(10).

I'm guessing I need to use some kind of static variable or array, but I don't really know how to do this.

View Replies!   View Related
Creating Static Cell Colors
How do I make the colors of certain cells on my spreadsheet static and unchangeable by cell data that may be copied and pasted from other cells of different color? Is this possible?

View Replies!   View Related
OFFSET Statement Formula Static
Is there a way to make the last two parts ("height" and "width") of the OFFSET statement formula static (meaning they alway refer to the same cell)? I am inputting a different number in the "colum" section of the formula and every time I changes which cells the "height" and "width" refer to.

View Replies!   View Related
Static Multiple Criteria Lookup
i need to track tyres, i'll have to use the Serial Nos. of the tyres inscribed on them. What i require is to lookup the serial no. from Purchase/issue sheet to my Maintenance sheet. To do that there'll be 3 criterion to match to get the correct Serial No.

In my Purchase sheet suppose if a purchase a tyre and issue it to some vehicle on 11th September, i'll have its first inspection for maintenance after mebbe a month (no fixed intervals). So if i check on 10th October the lookup should actually find the greatest date lesser than the inspection date and keep it to that. I know that the lookup function would get the last unit in case of duplicate entries but that becomes dynamic and the serial no. would change once theres another entry matching the 3 criteria. And dat would mess up my previous record.

View Replies!   View Related
Open A Workbook That Has A Static Address
I'm trying to open a workbook that has a static address.

View Replies!   View Related
Converting Formula To Static Code
Converting formula to static code. I am currently using the following code on a spreadsheet:

View Replies!   View Related
Global Static Variables On Workbook_open
problems with Global Static Variables.

In the Workook_open Sub I want to define 2 global static Variables.
For some reason I can't get it to work.
Whenever I try to access these vars they have the value "0"
I use Excel 2000.

Here is what i did:

Dim i As Integer
Dim ws As Worksheet
Public Static GlobalStartX As Integer
Public Static GlobalStartY As Integer

Private Sub Workbook_Open()

GlobalStartX = 6
GlobalStartY = 21

End Sub

View Replies!   View Related
Static Random Number On Demand
I am using =randbetween(1,100). however everytime i enter a new number elsewhere on the sheet, a new ndom number is made. Could you make a button (or something) that, when pressed, creates a random number in a certain cell?

View Replies!   View Related
IF And Date Function
I have a spreadsheet which has a date in a column, day in another and an IF function related but the if function does not seem to be working, can anyone assist? here is an example of how it is setup Cell A1 has a date e.g. 31/01/2009. Cell A2 returns the day of the week function =WEEKDAY(A1,1) formatted to custom to return the DAY of the week using dddd. Cell A3 has the IF function e.g. =IF(A2="Saturday",0.1,0). Cell A3 is the PROBLEM as all this ever returns is 0 (false) and never returns 0.1.

View Replies!   View Related
Date Function
I am trying to figure out what function automaticly updates a date in a cell everytime the workbook is opened.

View Replies!   View Related
Save Static Copy Of Workbook. No Formulas
Is it possible to use VBA code to save a copy of your workbook while removing all formulas and replaceing them with values and formats only? I know how to save and rename, but can;t find the command for a "Static Copy".

View Replies!   View Related
Dropdown Or Combobox With Static Space Between Text
I have a set of date of different lengths that I would like in a dropdown or combobox in a grid like fashion. Example

Apple, Banana, Orange, Kiwi
Mangos, Pineapple, Passionfruit, Guava

and I am trying to get it into the dropdown/combobox as


(without the dashes, the forum does the same thing excel does)

The data is of varying lengths, and the lists are actually parts lists, so they are long and of varying lengths each. Aside from physically going in a manually padding spaces, which isn't feasible given the number of them. The have alphanumeric characters, so jumbled all together seperated by commas is very messy and hard to read. I have tried padding with spaces using a formula but they do not line up right. I have been searching for a solution either having the entries in separate cells and concatenated entries, and still no luck.

View Replies!   View Related
Anyway To Make Auto Fill Time Value Static
I have 2 cell, A1 and B1. When any data is entered into A1, B1 will display the time corresponding to when the data was entered.

I was thinking about function for B1 would be "=IF(A1="","",now()) ". It worked such that it will display the time, however the value in B1 will automatically update itself if I were to randomly input any data in any other cell too.

For example, I input "testing" in A1. B1 display "11:20PM". Ten minutes later, I input "hello" in A2. B1 now display "11:30PM".

Anyone know a way to stop B1 from updating? or only update when new data is entered into A1 only?

View Replies!   View Related
Static Check Boxes With Time Stamp
I have a spreadsheet in which a check box fills the corresponding cell with the value "TRUE" if checked and "False" if unchecked. I use this check box for several functions (e.g. assigning a unique ID number). One of the functions I would like to make is a time and date stamp in another column when a user checks a box.

I have perused some of the past topics on this subject and there are a few hiccups in the other fixes.

1. I can't simply set the iterations to one because this sheet also is used to calculate expenditures, fund balances, etc. I need the calculations to be ongoing.

2. I currently have a check box macro that placed a check box on a thousand rows. It may require more rows later on. I need a solution that sets up a time stamp for all of these rows, not just a macro for one row at a time.

3. I need the time stamp to be static. I need to know when entries were made. I cannot use track changes, because we will be using this data to measure the effectiveness of our process. I need to be able to add and subtract the time between the timestamps.

View Replies!   View Related
Change Macro From Static To Relative Location
I have a file that has 114 rows of data. One for each office I'm working with. I need to insert 6 rows in between each of those rows and type text into some of the fields. I recorded a macro (since I don't know how to write one), but it uses a static location for the rows and data, as opposed to a relative location. So, what happens is, I get the same data entered over and over again. Also, even if I get this to work right with a relative location, I still would have to run it 114 times. I'd like to create one macro that would do the entire document. Here's what I've recorded: .....

View Replies!   View Related
VBA Copy Data Between Non-static Workbooks
Objective: Copy filtered contents between two workbooks.

Reason: I spend HOURS copying and pasting data between workbooks. We get files on average twice a week that contain multiple tabs of data that must be broken down and sent out to the end-users.
Example: Monday filename=neverconstant.xlsx, 11 tabs of unique data, 110 users that need the data but business dictates user1 can only see user1 data so I must filter user1 in tabs 1-11, copy raw data, put into a new workbook while naming tabs same as neverconstant.xlsx.

Thursday filename=stillnotconstant.xlsx, 8 tabs of unique data, 110 users that need the data but business dictates user1 can only see user1 data so I must filter user1 in tabs 1-8, copy raw data, put into a new workbook while naming tabs same as stillnotconstant.xlsx.

My biggest hangup is the filename is never constant. I am trying to make something that would add a workbook, add and name necessary tabs, copy and paste data into respective tabs and prompt me to "save as" with the new workbook. I am still VERY new to vba so please dont laugh to hard when you see what I have so far.

View Replies!   View Related
Delete Rows Not Matching 3 Static Criteria
I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.

Sub TestDeleteRows()

Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With

View Replies!   View Related
Static Time Stamp For Active Cell
I need VBA code for a time stamp. I just need code that will allow me to have a time stamp inserted in the current cell.

View Replies!   View Related
Excel Date Function
Kindly help me with the attched file.

I am trying to divide the months into weeks so I could easily identify if a particular date of a month falls on 1st,2nd,3rd or 4th week of the month.

View Replies!   View Related
If Function: Change In Date
i have date values in say, cells a1 to a4

20080522 21:00:00
20080523 14:45:00
20080523 15:00:00
20080523 15:15:00

i would like cell b to tell me when there is a change of date (not change of time), ie insert a 1 in lets say cell b2. i know it will be a simple if function but i dont know how to only read the first 8 digits

View Replies!   View Related
Find Value By Date Within Function
I am comparing two series of data, a daily series and a weekly series, to make calculations. The "F" column is daily data and the "I" is weekly.

The formula I am using is:
=IF(AND( SUM(I58:I62)<F62,I63>F63),B64,0)

The data I need to use in the "F" column in place of "F62" is always on a Friday.

I had taken "F62" which is the preceding working day's data. In fact I need to take the preceding Fridays data in column "F" as I need to compare the week to week trends. Due to bank holidays I cannot count back form F62 to get the data. There is always end of week data recorded on Fridays even though it may be a bank holiday.

I have the dates in the "H" column. Can a date reference be used in the formula to find the Friday data in column "F"? Or is there another solution to this problem?

View Replies!   View Related
Date Function/Text
In column A there is a date and time function in the format as follows :
dd/mm/yy hh:mm:ss most of the time.

When this is downloaded it is easy to manuplite. But some times it show as
above but as a text string or sometimes the dd/mm is switched to mm/dd.

Does anyone know the work rounds these formulas?

I use the dd/mm and Hr function to complie reports from column A but usually
spend hours using mid() left() function due to the date change.

I have tried formating the cell but this does not work?

View Replies!   View Related
Date Function Is Not Working..
I have set of data in which i want to put filter Milestones Type <> Milestone
and a filter Actual date is greater than or equal to 30 days ago and is less than or equal to today.

View Replies!   View Related
Date Function, For A Tracker
I've entered there name in column A, and the expiry date in column B. How do i then get column C to show how many days or months are remaining? Ideally i would have the guys with 3months or more left in green 1-2months amber and <1month in red.

View Replies!   View Related
IF,AND, Function Displaying Last Date
I have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date. This is what I have so far: =IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C)).

I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in. So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".

View Replies!   View Related
Copyright © 2005-08, All rights reserved