When you need to calculate the difference between a time present day and the next day, calculations can get messed up and you will see lots of # characters. This article shows how to create a time calculator in Excel that can calculate shift time after midnight using the IF function.

Syntax

=IF( end time > start time, end time + start time, end time + start time + 1 )

Steps

1. Start with the IF function =IF(
2. Enter the condition to compare the start and end dates D3>C3,
3. Continue with the condition if end date is greater D3-C3,
4. Final argument is the reverse condition D3-C3+1
5. Close the function )
6. Press the ENTER key to finish the formula.

How

Excel keeps date and time values as numbers. It assumes the history starts from Jan 1st, 1900 and accepts this date as '1'. While whole numbers represents days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 0.5.

When working with numbers you can subtract a large number from a small one, and convert it to positive number by multiplying by -1 or using the ABS function. However, this approach doesn't work with time and date data. Even though Excel treats them as numbers, they start from 0 (1/0/1900, which is not even a real date). Hence, there isn't a negative concept for date/time values and we need a workaround.

This is why we need to add 1 to the subtraction. Because 1 indicates a day and 1 day equals 24 hours. Hours use 24 base instead of 10, so adding 24 to the time value for the next day is similar to getting to double digits on a 10 base.

As a result; the idea is that if the end value is greater than the start value, do regular subtraction, if not then add 1 to the subtraction.

=IF(D3>C3,D3-C3,D3-C3+1)