Select the cells and press Ctrl + Shift + # (Ctrl + Shift + 3 on a US keyboard). Excel applies the d-mmm-yy date format, so the value 45435 displays as 23-May-24. This is the Windows shortcut; check it on a Mac before relying on it.
For any other layout, press Ctrl + 1, choose Number → Date and pick a type, or go to Custom and type a code such as dd-mm-yyyy or dd mmm yyyy. Short Date and Long Date in the Home → Number Format list follow the Windows regional settings, so they can look different on someone else’s PC.
The shortcut changes only how a number is shown; it does not turn text into dates. If your imported dates are text (they usually sit left-aligned), select the column, go to Data → Text to Columns, click Next twice, choose Date with the order your data uses, such as DMY or MDY, and click Finish. Then apply the format.
If you see ######## after applying the format, either the column is too narrow (double-click the right edge of the column header to widen it) or the number is too large to be a date, such as 20240523 stored as yyyymmdd. Convert that kind of value with =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) and format the result.
Latest Video Tutorial – 10 Excel Keyboard Shortcuts (Probably YOU are NOT Using)
Manytimes when you extract the data that has dates as values in the cells from any tool, ERP, or Software, there could be a possibility of having that values in numeric or non uniformed date formats.
To make those values into a standard date format, you need to apply the date format using the quick keyboard shortcut, which will convert the values into a standard date format.
Keyboard Shortcut to Apply Date Format in the Excel
Ctrl + Shift + #
Steps to Apply Date Format Using Keyboard Shortcut
- First, select the cells or the column that have dates as values.
- After that, simply press the “Ctrl + Shift + #” keys together and your date values will get converted to a uniform date format.
Is there a way to change which date format Excel chooses as the default? The other keyboard shortcuts to apply formatting seem to choose the top result from the applicable category of the Format Cells window (e.g. ctrl + shift + ! formats as number with 2 decimal points, ctrl + shift + $ formats as currency with 2 decimals, ctrl + shift + @ formats as H:MM:SS AM/PM, but ctrl + shift + # doesn’t format as the top date 3/13/2012)