In today’s data analysis tutorial, we’ll learn how to use Microsoft Excel 365 / 2019 / 2016 to split a date cell into year, months and days values using formulas.
In a nutshell, we’ll go through several cases in which we’ll convert a full date which format is mm/dd/yyyy hh:mm:ss into shorter date forms.
Note: Although written on a Windows computer, the tutorial is fully compatible with Excel for macOS.
Get the year value from date
Our first case will be to find the year value. We’ll use the YEAR function.
=YEAR (A3)And in our case:
Extract the month from a date
In this example, we would like to quickly find the month value. We can use the MONTH formula function to get the month number:
=MONTH(A3)
But what if instead of obtaining the month number, we would like to actually go and extract the month name?
In this case, we should use the TEXT function, which is a a general formatter Swiss army knife and does a great work formatting date values.
Abbreviated Month Names (such as Jun, Jul etc’):
=TEXT(A3,"mmm")
Full Month Names:
=TEXT(A3,"mmmm")
Split and combine date to month year format
We can use the TEXT function to format our dates and including separators such as /, -, : etc’. In this case we’ll extract the mmmm-yyy format from our date value. We can obviously pick additional date formats as needed.
=TEXT(A3,"mmmm-yy")
Extract the Day value
Our last case for today we will get the day value:
=DAY(A3)