Date automation


I would like Cell L to automatically populate the date in the format of MM-YY when a date is entered into Cell K. How do I do this? 




Enter this formula in column L.

[L2] = IF(ISNUMBER(K2,K2,""))

This will leave K2 blank if a non-numeric value is entered in K2. A true date is a numeric value. For example 30 July, 2020 equals 44042. This value you can format any way you like. Excel will do this automatically. For example, if you enter 30 July, 20 in a cell with General format (the format Excel gives to cells by default) the cell will display something like 7/30/2020 which is the date you entered in the "short date" format set in your Regional Settings. Some novices work very hard to let the cell show exactly what they entered, i.e. 30 July, 20 and they end up with a fake date which is text, non-numeric and basically useless.

The correct way is select Format Cells from the Home tab or the right-click context menu. To give a format like 30 July, 20 you can probably select one of the pre-programmed Date formats from the Number tab. Whatever isn't available there by default you can make up using a Custom format. The format mm-yy would be a Custom format. Once that format has been applied to K2 the same date that shows as 30 July, 20 in K2 and 4402 in K2 will change its face to 07-20. 

You may prefer to apply that format directly to K2 and dispense with the copy in L2 if your setup really intends to have two dates side by side. However, using different formats on several copies of a date in different places of a form is common practice.


Answer the Question

You must create an account to use the forum. Create an Account or Login