» Quickly Typing Dates into Cells
CATEGORY - Excel Editing
VERSION - All Microsoft Excel Versions
Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:
=DATE (YEAR(TODAY()), MONTH(TODAY()), A1)
Type a full number in the cell and change it to a date by using the following formula:
=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"
&RIGHT(A1,2))
For example, type 122203, the result is 12/22/03.
Book Store:
answer to confused and another comment
chanebaum
Answer to confused: the 22 is the day of the month, you could modify the formula not to need this entry by replacing "A1" with "day(today())".
Of note, the String formula will only work correctly if the date is entered as a 6 digit number - e.g. you would need to enter the 5th as 05... but this will present another problem to novice users, as the usual formating on excel cells will drop the 0 preceding a number. I would propose the following formula:
=IF(LEN(A3)=6,(LEFT(A3,2)&"/"&MID(A3,3,2)&"/"&RIGHT(A3,2)),(LEFT(A3,1)&"/"&MID(A3,2,2)&"/"&RIGHT(A3,2)))
Extra column
lewmac
This requires an extra column on a table. I guess it could be hidden for print but it would have to be done each time. The top formula worked OK for me but the bottom one gives me a message #VALUE. I have no trouble when I do the sections individually, such as =VALUE(MID(A8,4,2)) -- in row 8.
Error
lewmac
Re the #VALUE message above: I had the cell set at General instead of a date format!!!! It works OK.
Thanks!