Results 1 to 7 of 7

Thread: Find Today

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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
  •