Did you ever come across a situation where you had to compare two dates ignoring their time part to arrive at a status indicator ? Let’s see an example and the trick to address it:
Example Scenario: Here you have some order ids and their respective order dates, target dates and delivery dates as in the below screenshot. Now, you are asked to arrive at the order status using a business rule that states – “If the delivery date is on or before target date, disregarding the time of the day, then update the status as ‘Target Met’ else update it as ‘Past Due’.”
so the business rule when translated into Excel formula, looks like this: =IF(D2<=C2, “Target Met”, “Past Due”)
When this formula is entered in cell E2 and copied down till E8, the order status looks as in the below figure:
As per the business rule, the comparison of Order Date and Delivery Date should ignore time part of the day. Irrespective of what time of the day the order/product has been delivered, if the delivery date falls on the target date, the status should be updated as ‘Target Met’.
However, the status’ for Order IDs 12126 and 11231 are updated as ‘Past Due’ although they are expected to be updated as ‘Target Met’.
Challenge: While comparing Order Date and Delivery Date, Excel is comparing the complete date and timestamps.
Solution: To let Excel consider only dates and ignore timestamps while comparing, truncate the timestamps from dates using INT function, before doing the comparison. So the formula becomes: =IF(INT(D2)<=INT(C2), “Target Met”, “Past Due”)
Now the result appears as desired as in the below figure.
Explanation: All dates and timestamps in Excel are actually serial numbers. 1-Jan-1900 is treated as 1, 2-Jan-1900 is treated as 2 and so on. While Excel also stores timestamps as fractional numbers between 0 and 1. 0 represents 12:00:00 AM (start of the day) and 0.999 represents 11:59:59 PM (end of the day).
Using this process, in our example, for the Order ID 12126, the numerical representation of Order Date becomes 41443.66 and for Delivery Date, it becomes 41443.68. When these two values are compared, Delivery Date (41443.68) is not less than Order Date (41443.66), so the status is updated as ‘Past Due’.
INT() is a function in Excel that takes a number as input and returns the number rounded down to the nearest integer. i.e., for Order ID 12126, INT(D4)<=INT(C4) becomes INT(41443.68)<=INT(41443.66) which evaluates to 41443<=41443 which is TRUE. Hence the status gets updated as ‘Target Met’.
Hope you enjoyed reading this post..!! Want to master Excel with more such techniques..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right or simply click on the FOLLOW button at the bottom right of the window, if it appears in your browser.
Do leave your comments or questions, if any. Happy learning.. 
Leave a Reply