The time-dependent density functional theory (TD-DFT) calculation of excitation spectra places certain demands on the DFT exchange-correlation potential, upsilon (xc), that are not met by the functionals normally used in molecular calculations. In particular, for high-lying excitations, it is crucial that the asymptotic behavior of upsilon (xc) be correct. In a previous paper, we introduced a novel asymptotic-correction approach which we used with the local density approximation (LDA) to yield an asymptotically corrected LDA (AC-LDA) potential [Casida, Casida, and Salahub, Int. J. Quantum Chem. 70, 933 (1998)]. The present paper details the theory underlying this asymptotic correction approach, which involves a constant shift to incorporate the effect of the derivative discontinuity (DD) in the bulk region of finite systems, and a spliced asymptotic correction in the large r region. This is done without introducing any adjustable parameters. We emphasize that correcting the asymptotic behavior of upsilon (xc) is not by itself sufficient to improve the overall form of the potential unless the effect of the derivative discontinuity is taken into account. The approach could be used to correct upsilon (xc) from any of the commonly used gradient-corrected functionals. It is here applied to the LDA, using the asymptotically correct potential of van Leeuwen and Baerends (LB94) in the large r region. The performance of our AC-LDA upsilon (xc) is assessed for the calculation of TD-DFT excitation energies for a large number of excitations, including both valence and Rydberg states, for each of four small molecules: N-2, CO, CH2O, and C2H4. The results show a significant improvement over those from either the LB94 or the LDA functionals. This confirms that the DD is indeed an important element in the design of functionals. The quality of TDLDA/LB94 and TDLDA/AC-LDA oscillator strengths were also assessed in what we believe to be the first rigorous assessment of TD-DFT molecular oscillator strengths in comparison with high quality experimental and theoretical values. And a comparison has been given of TDLDA/AC-LDA excitation energies with other TD-DFT excitation energies taken from the literature, namely for the PBE0, HCTH(AC), and TDLDA/SAOP functionals. Insight into the working mechanism of TD-DFT excitation energy calculations is obtained by comparison with Hartree-Fock theory, highlighting the importance of orbital energy differences in TD-DFT.