Flag reversed start and end times

Turn 2:30:15 PM to 4:00:15 PM into 1:30:00. An end before the start says Review order.

What you'll make

In Excel: 2:30:15 PM to 4:00:15 PM becomes 1:30:00; reversed becomes Review order.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesFor row 8
1B8<A8Checks if the end is before the start.FALSE
2"Review order"The message when the end comes first.Not used for row 8
3B8-A8Takes the start away from the end.1:30:00
4IF( … )Shows the message, or else the time between.1:30:00

Both times must be on the same day. An end before the start is not taken as the next day.

=IF(B8<A8,"Review order",B8-A8)

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own times. Paste start times and end times into the green columns of the Template tab. The time between fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Check every Review order row. Swap the times if they were typed the wrong way round.

Microsoft's help: IF function