Read 24:00 as midnight the next day
Turn November 4, 2026 at 24:00 into November 5, 2026 12:00 AM.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | B8="24:00" | Checks if the time is exactly 24:00. | TRUE |
| 2 | INT(A8)+1 | For 24:00, gives the next day at 12:00 AM. | November 5, 2026 12:00 AM |
| 3 | INT(A8)+TIMEVALUE(B8) | Otherwise adds the time to the date. | Not used for row 8 |
| 4 | IF( … ) | Uses part 2 for 24:00, part 3 for any other time. | November 5, 2026 12:00 AM |
Read it left to right. 24:00 means the end of the day, so it becomes the next day's midnight.
=IF(B8="24:00",INT(A8)+1,INT(A8)+TIMEVALUE(B8))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own dates and times. Paste dates into the first green column and times like 14:30 or 24:00 into the second. The dates and times fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use 24:00 only when your list means the end of that day. Other times run from 00:00 to 23:59.
Microsoft's help: IF function



