1. ## Find Today

Excel 2000, I keep some logs which I set up at the beginning of the year with 365 dates running down column A allowing me to fill in various items associated with those dates. It would be awfully nice to be able to jump to today's date, but I've not found an easy way to do it and I'm not a programmer. Is there something fairly simple? Thanks.

2. ## Re: Find Today

According to <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q95/0/78.ASP>Q95078</A> the following formula finds the current day number:
= Int(((Now() / 365.255) - (Year(Now()) - 1900)) * 365.255)
However, when I run it (in Excel 2K) I get 71, but I reckon this is day 70...?

So, try this as a macro:

Sub JumpDay()
a = Int(((Now() / 365.255) - (Year(Now()) - 1900)) * 365.255)
Cells(a - 1, 1).Activate
End Sub

If my maths is wrong, make it 'Cells(a, 1).Activate'
Let me know if you need help installing it as a macro.

3. ## Re: Find Today

This will not allow you to jump to today's date, but it allows you to sormat today's date in a special format, so that it attracts your attention.
Select the column and choose Conditional Formatting. Set the condition Cell equal to =Today() and select some nice formatting (e.g. red bold font, grey pattern)

4. ## Re: Find Today

Here is another way. Using Insert, Name, Define create a range name, say "tday". While the dialog screen is still open, type the following into the Refers to Box at the bottom:
=OFFSET(sheet1!\$D\$5,MATCH(TODAY(),D5370,0),0,1)
D5 is the first date of the series, the top of the fill, (1/1/01). Then use the Edit, GOTO function and type in tday in the "Reference" box each time you want to jump to today's date in the list.

5. ## Re: Find Today

Hi sanora,

If you place this in the code of the Worksheet_Activate Event, I believe this will work for you:

Private Sub Worksheet_Activate()
Dim a As Integer 'row where dates start
Dim b As Integer 'row where dates end
Dim c As Integer
a = 1
b = ThisWorkbook.ActiveSheet.UsedRange.Rows.Count
For c = a To b
If Cells(c, 1).Value = Date Then ActiveWindow.ScrollRow = c
Next
End Sub

You need to change "a" to whatever row your dates begin at (I did not know if you might have some header rows). Each time you activate the sheet, the row for today will be at the top of the screen.

If you wanted to use the calendar control, you could also use the calendar click event to select which day you would like to see by clicking the calendar.

HTH,
Mike

6. ## Re: Find Today

I have not had a chance to try all these ideas, but I do appreciate your answers. I don't know if I'd want to have today's row at the top, but I'm excited about the calendar control. Never thought of that. Thank you all for your time.

7. ## Re: Find Today

Hi,

I was intrigued by your solution so I tried it. 3 things I observed:
- if jan 1 is in cell, say d5, then the formula needs to start in d4 for the first part. otherwise my goto is a day later. Am I doing something wrong here? I copied and pasted the formula from your email to my "refers to" box, so no re-typing involved.
- I had to put in \$ signs to make the cell refs in the offset absolute; else the 2nd use of the formula ended up with a dif cell range, which I didn't understand.
- didn't quite follow the args to offset. I know how offset works (I think). So
- the sheet1!\$D\$5 is the starting cell,
- Match says how many rows to go down (so jan 1 is 1st row and that is why the first arg to offset has to be 1 row above jan 1),
- 0 says to go 1 col over from starting point yet I end up in same col,
- 1 says to create a range of cells 1 row high
- no col width ref given.
I tried adding a col width arg and got errors so I deleted it.

Very useful approach. Thks.

Fred

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•