Work with the example
Can a report show the date and clock separately without parsing text? B5 holds the known numeric local date-time 2026-10-12 23:45. The supplied observations have whole-second precision. D5 takes the integral date; E5 rounds the fractional day to a whole second for the clock view. The source stays unchanged. There is no timezone conversion or subsecond-preservation claim.
| Output | Formula after numeric guard | Display |
|---|---|---|
| Date D5 | INT(B5) | 2026-10-12 |
| Clock E5 | ROUND(MOD(B5;1)*86400;0)/86400 | 23:45 |
| Recombined F5 | D5+E5 | 2026-10-12 23:45 |
Edit the working cells
E5 uses guarded ROUND(MOD(B5;1)*86400;0)/86400: multiply by 86400 seconds/day, round to a whole second and divide back to numeric days. Use yyyy-mm-dd on D5 and hh:mm on E5. Change B5 to October 13 at 00:15: D5 becomes October 13 and E5 shows 00:15; F5 equals the changed source. A fresh exact midnight gives numeric 0 in E5, displayed 00:00. Clearing B5 makes D5/E5/F5 say Date-time missing.
Check the failure case
DAY(B5) gives 12 at baseline, just the day within a month. It is not the complete date and cannot reconstruct the timestamp. The separate results remain numeric for later arithmetic; the clock view uses the stated whole-second rounding.
Does a clock value 0 mean that the source date-time is missing?
Show the worked answer
No. A known numeric midnight has a valid zero fractional day.