Non-Negative Time Calculation in Excel

Excel Formula to Calculate Time with Non-Negative Output

Objective

To create an Excel formula that calculates the difference in time, ensuring that the result cannot be negative.

Provided Formula

The original formula provided is:

=IF([@[Best ETA from previous port]]>=[@[DP - PROFORMA ARRIVAL]], 24*([@[TIME_actual_arrival]]-[@[Best ETA from previous port]]), 24*([@[TIME_actual_arrival]]-[@[DP - PROFORMA ARRIVAL]]))

Enhanced Formula to Prevent Negative Results

To ensure that the output is non-negative, we can use the MAX function. The modified formula is as follows:

=MAX(0, IF([@[Best ETA from previous port]]>=[@[DP - PROFORMA ARRIVAL]], 24*([@[TIME_actual_arrival]]-[@[Best ETA from previous port]]), 24*([@[TIME_actual_arrival]]-[@[DP - PROFORMA ARRIVAL]])))

Explanation of the Formula

• IF: This function evaluates a logical condition. If the condition is true, it executes the first calculation; if false, it executes the second.

  • Condition: [@[Best ETA from previous port]] >= [@[DP - PROFORMA ARRIVAL]]
  • True Statement: Calculates the time difference based on the Best ETA from previous port.
  • False Statement: Calculates the time difference based on the DP - PROFORMA ARRIVAL.

• 24*(...): This part converts the time difference from days (or fractional days) to hours.

• MAX(0, ...): This function ensures that the result of the IF statement is not negative by taking the maximum value between 0 and the calculated time difference.

Practical Example

• Assumptions:

  • Best ETA from previous port: 4/12/2023 10:00 AM
  • DP - PROFORMA ARRIVAL: 4/12/2023 11:00 AM
  • Actual arrival time: 4/12/2023 12:00 PM

• Calculations:

  • TIME_actual_arrival = 12:00 PM on the reference date.
  • If Best ETA is later than DP - PROFORMA ARRIVAL:
    • The formula would calculate: 24 * (12:00 PM - 10:00 AM) = 48 hours
  • If DP - PROFORMA ARRIVAL was used:
    • The calculation would be: 24 * (12:00 PM - 11:00 AM) = 24 hours

• Final Output: The output is calculated as the maximum of 0, ensuring no negative results.

Conclusion

The enhanced formula provides a robust solution for calculating time differences in hours while preventing negative results. Utilizing the MAX function in conjunction with the IF statement allows for accurate and user-friendly outcomes in various data scenarios. For deeper understanding and practice, consider exploring the courses available at the Enterprise DNA Platform.