Forum Home
Press F1
Thread ID: 59065	2005-06-20 20:47:00	Fixing Excel Date values	Tony (4941)	Press F1

Post ID	Timestamp	Content	User
365515	2005-06-20 20:47:00	I have a spreadsheet that I update every day with a new row of values. They are identified by date. I would like the date of entry to be entered automatically. OK use the TODAY function, I hear you say. But the problem is that this is dynamic, i.e. tomorrow when I open the workbook to update it, the date has changed (to today, not surprisingly!) How do I get Excel to supply today's date, but then keep that as a constant, rather than updating it every day? I'm sure this has to be easy, but I can't work it out. :confused:	Tony (4941)
365516	2005-06-21 11:26:00	Hi there One way springs to mind. You could put a start date in some place that you could hide without affecting the rest of your sheet, then put a formula in the other cells you want to show the date which would be something like = (cell you have start date in)+1 day (cant remember the correct wording but). May need to work on it to get it to work right. Will give it more thourght.	craigb (4884)
365517	2005-06-21 11:42:00	OK here goes. I am assuming that when you put the data in you want to have the date pop in as if by magic. I am also assuming you don't want to see the date showing for the day there is no data. If you put a start date at the top of your sheet in the first row say a1. This is the only row of data that you will need to enter the date for. You then put in the next row (in b1) =if(b2="","",a1+1) You then fill this formula down to the bottom of the page so you should end up with =if(c2="","",b1+1) =if(d2="","",c1+1) =if(e2="","",d1+1) etc down to the end of the page. This will give you a blank cell in the date column untill you put data in at which time the date will pop up. Will that work for you? Cheers Craig	craigb (4884)
365518	2005-06-21 11:57:00	'Fraid not, craigb. Your solution assumes the date is going to constantly increment by one. While this is probably the case, it can't be assumed. Although I said in the OP that I updated every day and that is the intention, sometimes the spirit is willing but the body forgets! :( You're right about the springing into view, though. Unfortunately, I already know how to do that bit! :D I do appreciate the effort you made in developing the formulae - thanks.	Tony (4941)
365519	2005-06-21 21:28:00	I actually thought I posted this reply yesterday, but somthing bad (tm) must have happened. Create a simple macro to do it. Then bind the macro to a key like <ctrl><shift>D Sub InsertToday() ActiveCell.FormulaR1C1 = Date End SubWould be enough Hope this helps -Qyiet	qyiet (6730)
365520	2005-06-22 03:48:00	I think there's a predefined shortcut to enter the date: ctrl + ; . Wouldn't that be as easy to use? But I suspect there's a wee problem which isn't going to be easy to get around "automagically" . This shortcut and the macro enter today's date . Assume we already have records for Sunday and Monday . It's now Wednesday . We will get Wednesday's date . But one way to read Tonys question is that he might want to enter Tuesday's record on Wednesday . :(	Graham L (2)
365521	2005-06-22 04:17:00	I think there's a predefined shortcut to enter the date: ctrl + ; . Wouldn't that be as easy to use? Hmm, I never knew that shortcut existed . . it sure would be easier . Thanks -Qyiet	qyiet (6730)
365522	2005-06-22 05:08:00	All these great responses have given me some ideas. The shortcut is great - I didn't know it existed either - but what I want is an automagic method. The macro is also great, but requires to be manually invoked. What I would like is to have a formula in A3 that says something like IF (isblank(B3)"",inserttoday()) So when I load a value into b3, the date appears in A3. It seems to me I ought to be able to do it with a user-defined function (called inserttoday), but I can't make one work. Or maybe I just don't know what I am doing (quite likely, as I don't use Excel VBA very often).	Tony (4941)
365523	2005-06-22 05:40:00	The shortcut is great - I didn't know it existed either - but what I want is an automagic method . . . What I would like is to have a formula in A3 that says something like IF (isblank(B3)"",inserttoday()) Ah . . I see . Try this, you will have to add it to the code on the sheet you want it to work on . The correct place to put it should look something like this: VBAProject(spreadsheetname . xls) ->Microsoft Excel Objects ->SheetName(Sheet1) Private Sub Worksheet_Change(ByVal Target As Excel . Range) If Intersect(Target, Range("B3", "B150")) Is Nothing Then Exit Sub If IsNull(Target . Value) Then Exit Sub 'Else Cells(Target . Row, Target . Column - 1) . Value = Date End Sub -Qyiet	qyiet (6730)
365524	2005-06-22 06:01:00	On a completely different slant . Couldn't you make a macro that copies the cell velue (todays date) and using paste special paste it back as a value and not a formula? So click on the cell, run the macro and convert from being a formula to a value . . . just an idea . . .	netchicken (4843)
1 2