Search code examples
excelformula

Excel formula to compare date/time and report if it is less than 24 hours

I'm trying to create an excel formula to check the difference between cells C2 (opening time) and O2 (closing time) and add value within 24 hours or over 24 hours

INC6531515  4/03/2024 20:56 5/03/2024 07:16 Within 24 hours
INC6531448  4/03/2024 20:24 5/03/2024 07:17 Within 24 hours
INC6531334  4/03/2024 19:43 5/03/2024 07:17 Within 24 hours
INC6531245  4/03/2024 19:07 6/03/2024 08:13 Within 24 hours
INC6531043  4/03/2024 18:04                 Within 24 hours

 =IF(O2-C2<=24, "Within 24 hours", "Over than 24 hours")

This is just reporting that everything is within 24 hours

Solution

  • Subtracting days returns a result in decimal days. Try subtracting the cell 6/03/2024 08:13 from 4/03/2024 19:07, this returns 1.545.... Thus, you formula should be =IF(O2-C2<=1, "Within 24 hours", "Over 24 hours").

    Extension to empty cells:

    =IF(OR(C2="",D2=""),"",IF(O2-C2<=1, "Within 24 hours", "Over 24 hours"))