Date & time › DAY, MONTH, YEAR63 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeDAYMONTHYEAR

DAY, MONTH, YEAR: split a birth date

Ayu, an administrator at a junior high school in Bekasi, has to fill in an online competition form that asks for each student's day, month and year of birth in separate fields. In the school data, the birth date is stored as one value in column B.

Task

Fill C2:C6 with the day of the month (1 to 31), D2:D6 with the month number (1 to 12) and E2:E6 with the year, all taken from the birth date in column B. Use DAY, MONTH and YEAR.

Cells to fillC2:C6D2:D6E2:E6

Required functionsDAYMONTHYEAR

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
A date in Excel is really a serial number. DAY, MONTH and YEAR read the day, month and year parts of that number.
Hint 2
Each function needs just one argument: the date cell. Write the formula on row 2 (pointing at B2), then copy it down.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

C2:C6

=DAY(B2)

Write it in C2, then fill down to C6.

D2:D6

=MONTH(B2)

Write it in D2, then fill down to D6.

E2:E6

=YEAR(B2)

Write it in E2, then fill down to E6.

Explanation

=DAY(B2), =MONTH(B2) and =YEAR(B2) pull out parts of the date without changing the original data. The results are plain numbers, so columns C to E use a number format, not a date format. A common slip: the result cells inherit a date format, so 14 shows up as 14/01/1900. The reverse is DATE(year, month, day), which builds a date from three numbers.

Starting worksheet: Sheet1
Row	A	B	C	D	E
1	Student	Birth date	Day	Month	Year
2	Raka	14/03/2012
3	Salsabila	02/11/2011
4	Bima	28/07/2012
5	Nayla	31/12/2011
6	Kevin	09/01/2012
7
8
9
10
11
12
13
14

Fill in the requested cells, then press Check answer.

Validation result