Mini calendar as a 'goto' date function?

[Ironman]

Hello

Firstly, I'm not looking for a date picker in the usual sense i.e. a calendar that pops up in a cell.

I have an approx 3,000 row diary worksheet with consecutive dates from A2:A3000.

I need to know if there is a way of creating a permanently visible mini calendar so when I select a date on the calendar then it will 'goto' that row/date in the worksheet.

I'd be over the moon if this was possible.

Many thanks!

 

[Eric W]

Not exactly what you're asking, but this may work:

I set up 3 spinner buttons from the Developer tab where you can scroll to the date you want. I used the Freeze Panes tool from the View tab to make sure that rows 1 and 2 are always visible. Then the formula in F2 is:

=HYPERLINK("#Sheet4!A"&DATE(E2,C2,D2)-A3+3,TEXT(DATE(E2,C2,D2),"mm/dd/yyyy"))

Then you select the date you want, and click on the hyperlink in F2, and it will take you to the right row.

 

[Ironman]

That looks fantastic Eric, even better and more compact than a calendar!

I've never used spinner buttons before though. I've created the buttons from the Developer tab but would you mind please telling me how I create day/month/year from those?

Many thanks again!

 

[Eric W]

Sure, from the Developer tab, click the Insert button and select the Spin button (top row, fourth from the left). Use the mouse to "paint" it in the worksheet where you want it. Right click on the Spin button and choose Format Control. On the Control tab, for the Month spinner, change the minimum value to 1, the maximum value to 12, and change the Cell link to C2. Repeat for the Day (max/min = 1/31, cell D2), and Year (max/min = 2015/2050 or whatever the range of your dates is, cell E2). If you want to get a little fancier, I can tell you how to, if you spin the month value up from 12, it'll roll around to 1, and the same for the month spinner.

 

[Ironman]

Many thanks Eric, that works perfectly (I actually located the min/max values in Properties rather than Format Control).

I'd definitely be interested in "rolling around to 1" with the day/month spinners.

The only issue I have is with a spare cell for the link!

Also, what is the significance of "-A3+3" in the hyperlink formula please?

Thanks again!

 

[Eric W]

I should have mentioned the -A3+3 part. On my sample sheet, the first date was in A3. So the formula takes the calculated date from the spinners, subtracts the value of the first date, and that gives you the offset from the first date. So on my sample sheet, the first date was 1/1/2021. If you pick a date of 1/2/2021, then subtract, that gives you 1. Then add 3 (since the first date is in row 3), and you get 4, which is the row that 1/2/2021 is on.

To get the rolling around effect, you need 2 more cells somewhere. But don't worry, they can be anywhere, even on another sheet, or hidden off to the side somewhere. So for the month spinner, leave it where it is, but change the linked cell to your new cell, let's say it's Z99. Also change the min/max values to 1/20000. Then fill in Z99 with 10000 (halfway). Then go back to C2 and put in this formula:

=MOD(Z99-1,12)+1

Do the same thing for the day spinner, using your other new cell, and 31 instead of 12.

 

[Ironman]

Many thanks again Eric.

I've hit a snag - when I click on the newly-created hyperlink I get "cannot open the specified file".

I think I know why this is. The dates in Column A are in Custom format ddd, d mmm yyyy e.g. Fri, 30 July 2021. There is no option in the Date format to change to this format and I don't want to change this format from the way it looks now.

Is there any way around this?