This Excel tutorial explains how to compare time or apply condition on timestamps in Excel spreadsheet
Excel compare time or apply condition on timestamps
When we have two timestamps (a date with time) on the same date, we can use simple subtraction on the two timestamps to compare.
If the two timestamps have different dates, we can still minus the timestamps to compare.
But if we only want to compare the time only without considering the date, we need to extract the time portion of timestamps to compare another time. I will demonstrate how to do this in the below example.
Example – compare time
Let’s say we have the following timestamps, our goal is to determine whether the time is morning, afternoon, or night. Therefore the comparison is not date specific, we have to standardize the time in order to do comparison.
Step 1 – Define morning / afternoon / night
To define morning, the condition is time >=0:00 to <12:00 (0am-12pm)
For afternoon, the condition is time >=12:00 to <18:00 (12pm-6pm)
For night, the condition is time >=18:00 to <24:00 (6pm-12am)
Next, convert the time to numerical value, by dividing the time (in hour) by 24 hours. The reason is that the underlying value of time is a numerical value, value 1 means 24:00 on date 0/1/1900. Click here to see my previous post to learn more.
Therefore, the condition in Excel formula should be expressed as
Morning condition: AND(time>=0, time<0.5)
Afternoon condition: AND(time>=0.5, time<0.75)
Night condition: AND(time>=0.75, time<1)
Step 2 – Convert timestamps to date 0/1/1900
The second step is to standardize the Timestamps to date 0 of year 1900 using Mod Function. Click here to see the reason why using MOD Function.
Step 3 – Combine the condition
Finally apply the condition on the result of MOD. In fact, you can do everything in one formula, but the formula would be very long.
