Calendar Control linked cell format

**natefarm** · 12-06-2010, 06:47 PM

I added a calendar control to a sheet so the user can select the beginning date of a range. The LinkedCell value points to a cell on the same sheet. A formula on another sheet does a VLookup of that value. If I type a date into the cell, the VLookup works fine. If I select the date from the calendar control, it fails. Apparently the control enters the date into the linked cell as a text value (m/d/yyyy format) instead of a date value. I've confirmed this by changing the format of the cell to another format, but if I select a date from the calendar control, it still appears in the cell as m/d/yyyy. Is there a way to get the date from the control into the linked cell as a date value? I tried finding an event where I could correct it, but there is no event late enough in the process to capture the new value.

**davesexcel** · 12-06-2010, 09:03 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**natefarm** · 12-07-2010, 11:01 AM

Thanks Dave. I am attaching a sample workbook. I am running Excel 2003 under Windows XP.

The Weekly Chart sheet has a stacked chart showing production levels by day-of-week for a 4-week period starting Nov 1.

Note the formulas at the bottom showing "Includes Holiday" on Thursday, due to the November holidays. Click Nov 2 in the Calendar Control. "Includes Holiday" will now appear on Tue and Thur, and the values in the chart will be wrong. Double-click the From date (G3) and press Enter, and the correct values and formula results will appear. Click Nov 1 on the calendar and they will be wrong again.

Go to the Weekly Data worksheet, which is mostly formulas. Row 14 is the source data for the chart. Note how the format of the date in B3 differs from the other dates and results in failed Vlookup results in H3, N3, and T3.

Go back to the Weekly Chart and repeat the double-click/Enter of G3. The Weekly Data date format and lookup results will now be correct.