Date and time split or merged
Some rows keep the date and the time in separate columns and others pack both into one.
What it looks like
Before
| visit_date | visit_time | visit_at |
|---|---|---|
| 2024-05-03 | 14:30 | · |
| 2024-05-03 09:15 | · | · |
| 2024-05-04 | 2:05 PM | · |
| 2024-05-05 | · | · |
After
| visit_date | visit_time | visit_at |
|---|---|---|
| 2024-05-03 | 14:30 | 2024-05-03T14:30 |
| 2024-05-03 | 09:15 | 2024-05-03T09:15 |
| 2024-05-04 | 14:05 | 2024-05-04T14:05 |
| 2024-05-05 | · | · |
How it happens
- Two systems were combined, one with a single timestamp field and one with separate date and time fields.
- A form changed partway through its life, and old rows kept the old layout.
- People typed the time into the date cell when the time column was hidden or missing.
What it breaks
- Sorting by the date column alone puts events on the same day in the wrong order.
- A date column that sometimes includes a time is read as text, so date filters skip those rows.
- A missing time is filled in as midnight, which looks like a real reading and distorts anything measured by hour.
How to spot it
- Count values by length in the date column. Rows that include a time are longer.
- Check whether the time column is empty exactly where the date column is long.
- Look for a large number of events at exactly midnight. Real activity rarely clusters there.
How to fix it
- Decide the shape you need: one combined timestamp, separate columns, or both.
- Split packed values so each column holds only its own part, and write times in one style.
- Build the combined value only where both parts exist. Do not invent a time for rows that have only a date.
- Mixed date formatsOne column holds dates written in several different styles.
- Multiple values in one cellA single cell holds a list, or two different facts, joined by a comma, slash, or line break.
- Missing or mixed time zonesTimestamps in one column were recorded in different time zones, or carry no zone at all.