Monthly and quarterly figures come labelled Jan-24, Mar-24 or Q3 2024. Sorted as text, April comes before January; converted to a date, Jan-24 becomes the 24th of January or the 1st of January 2024, which is a day, not a month.
Column type: YEAR_MONTH
| In the file | Cleaned | Status |
|---|---|---|
Jan-24 | 2024-M01 | clean |
Feb-24 | 2024-M02 | clean |
Mar-24 | 2024-M03 | clean |
Apr-24 | 2024-M04 | clean |
May-24 | 2024-M05 | clean |
Column type: YEAR_QUARTER
| In the file | Cleaned | Status |
|---|---|---|
Q1 2024 | 2024-Q1 | clean |
Q2 2024 | 2024-Q2 | clean |
Q3 2024 | 2024-Q3 | clean |
Q4 2024 | 2024-Q4 | clean |
Q1 2025 | 2025-Q1 | clean |
A month stays a month and a quarter stays a quarter: the type is YEAR_MONTH or YEAR_QUARTER, written 2024-M01 and 2024-Q3, which sort correctly as text and say exactly which period a figure covers.
https://mcp.data-cleaner.com/mcp, then ask it to clean your file.curl -X POST https://data-cleaner.com/clean/v1/column \
-H 'Content-Type: application/json' \
-d '{"values": ["Jan-24", "Feb-24", "Mar-24", "Apr-24", "May-24"]}'
The answers above are the API's own, captured when this page was built.