CATEGORY: Excel Date & Time Formulas VERSIONS: All Microsoft Excel Versions

Problem: Entering dates quickly, without having to use delimiters to separate month, day, and year. Solution 1: Enter only the "day" part of the date and complete it by using the DATE, YEAR, TODAY, and MONTH functions in the following formula: =DATE(YEAR(TODAY()),MONTH(TODAY()),A2) Thus, on entering "5", the formula will automatically create a date representing the 5th of the current month in the current year. Solution 2: Enter the entire date, without delimiters, and use the DATEVALUE, LEFT, MID, and RIGHT functions as shown in the following formula to convert it to a proper date: =DATEVALUE(LEFT(D2,2)&"/"&MID(D2,3,2)&"/"&RIGHT(D2,2)) Thus, on entering "060705", the above formula will return "06/07/05". Problem: Entering dates quickly, without having to use delimiters to separate month, day, and year. Solution 1: Enter only the "day" part of the date and complete it by using the DATE, YEAR, TODAY, and MONTH functions in the following formula: =DATE(YEAR(TODAY()),MONTH(TODAY()),A2) Thus, on entering "5", the formula will automatically create a date representing the 5th of the current month in the current year. Solution 2: Enter the entire date, without delimiters, and use the DATEVALUE, LEFT, MID, and RIGHT functions as shown in the following formula to convert it to a proper date: =DATEVALUE(LEFT(D2,2)&"/"&MID(D2,3,2)&"/"&RIGHT(D2,2)) Thus, on entering "060705", the above formula will return "06/07/05".

Rate this tip 1 2 3 4 5   RATING: 3.40   VIEWS: 21147 Email Tip Print Tip Add to My Tips

READER COMMENTS (view all comments)

shawna wrote on December 31, 1969 19:00 EST Is there a way for the date to automatically update whenever the template is opened? Entering Dates Beverly Pastizzo wrote on December 31, 1969 19:00 EST When I enter the date 10-Sep in my date column and go top the next cell, the date turn into a five digit number that seems to have nothing to do with a date. What is wrong? I am new to Excel. entering dates quickly ajay wrote on December 31, 1969 19:00 EST IF I CREATE 31 DIFFERENT WORKSHEET AND WANT TO WRITE DATES ON SAME CELL OF EVERY WORKSHET IS SOLUTION IS =TODAY(),BUT IF I WANT DIFFERENT DATES ON EACH WORKSHEET AS SERIAL IE OON SHEET 1 1MAY, SHEET 2 2MAY, SHEET3 3MAY........... WHAT I NEED TO DO THANK YOU convert text '12/06/1985' in proper dates shiva wrote on December 31, 1969 19:00 EST i want to convert this text dates into proper date so can performed mat cal on that data Automatic update of date/time when clicking a cell D.L. wrote on December 31, 1969 19:00 EST I would like to be able to click on a cell to automatically populate it w/ the current time (or date). How do I do this? Can I use formulas, or are Macros involved (I am not experienced w/ Macros).

REGISTERED USERS - Click here to post comments GUESTS (Click here to register) Name Comment Title Comments

Definitive Guide to Excel VBA Good to Great: Why Some Companies Make the Leap... and Others Don't The McGraw-Hill Guide to Writing a High-Impact Business Plan: A Proven Blueprint for First-Time Entrepreneurs Investments + S&P Card + Powerweb + StockTrak discount coupon The One Page Business Plan: Start With a Vision, Build a Company! Fish! A Remarkable Way to Boost Morale and Improve Results

RELATED MICROSOFT EXCEL TIPS

Create User Forms in Microsoft Excel Using Loops in VBA in Microsoft Excel The Best Shortcut Keys in Microsoft Excel Writing Your First VBA Function in Excel Add Combo Box to a Sheet in Microsoft Excel Formulas to calculate Loan payments in Microsoft Excel Array variables using VBA in Microsoft Excel Create an Amortization Schedule in Microsoft Excel Find duplicates items in combine columns in Microsoft Excel Vlookup Formula – Organizing the Data Table

• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books