DATE from a yyyymmdd code
Cut the year, month and day out of a text code and rebuild a real date.
Task:
The bank file writes each value date as eight digits, such as 20240315. In B2:B6 turn each code into a real date.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of DATE
0/3The same formula in the other shapes it takes at work.
- DATE Function
Create dates from year, month, and day components.
CoreBeginner - DATE rolls over: the first of next month
Month 13 is January of the next year — DATE does the calendar arithmetic for you.
Intermediate - DATE from a yyyymmdd code
Cut the year, month and day out of a text code and rebuild a real date.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Value date (text) | Date |
| 2 | 20240315 | |
| 3 | 20231231 | |
| 4 | 20240229 | |
| 5 | 20240701 | |
| 6 | 20250110 |
What this exercise teachesMay contain the answer
Text functions cut the code apart and DATE assembles a real date that can be sorted, subtracted and filtered. DATE quietly converts the text pieces to numbers. Building the date from parts avoids any guessing about whether 03/04 means March or April.