Date & time › DATE27 / 67

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

EasyFreeDATE

DATE: combine day, month, and year

An elementary school in Bukittinggi receives registration forms that ask for the day, month, and year of birth in separate columns. The school operator needs one birth date column that is a real date, to calculate each applicant's age.

Task

In E2:E6, combine the day (B), month (C), and year (D) into a birth date with DATE, written in E2 and copied down to E6. Column E is already formatted as dd/mm/yyyy.

Cells to fillE2:E6

Required functionsDATE

Expected result

Loading the expected result…

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

Hint 1
The order of DATE arguments is year, month, then day: DATE(year, month, day).
Hint 2
On row 2, the year is in D2, the month in C2, and the day in B2.

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

E2:E6

=DATE(D2,C2,B2)

Write it in E2, then fill down to E6.

Explanation

=DATE(D2,C2,B2) returns a date value (a serial number) you can calculate with, for example an age with DATEDIF. A common mistake is following the column order, DATE(B2,C2,D2), which gives odd dates in the 1900s. Joining with & like B2&"/"&C2&"/"&D2 only gives text, not a date. Bonus: DATE rolls over values that go past the limit, so DATE(2019,13,1) becomes 1 January 2020.

Starting worksheet: Sheet1
Row	A	B	C	D	E
1	Name	Day	Month	Year	Birth date
2	Alif	14	3	2019
3	Bunga	2	11	2018
4	Chandra	29	2	2020
5	Dara	31	7	2019
6	Elang	5	1	2019
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result