Excel stores date value in integer number and time value in decimal number.
1 day = 24 hours
1 hour = 1/24 = 0.041666667
1 minute = 1/(24*60) = 1/1440 = 0.000694444
1 second = 1/(24*60*60) = 1/86400 = 0.0000115740740740741
To add seconds, minutes, or hours to a time, you can use the Excel functions or using the data above in addition formula.
Read the following article for a more detailed explanation of how Excel stores date and time
How to add SECONDS to time in Excel?
No Excel function solution
The formula
=A2+(B2/86400)
The result
Add the time value with the number 1/86400 and multiply by the number of seconds to be added.
Excel function solution
The formula
=TIME(HOUR(A2),MINUTE(A2),SECOND(A2)+B2)
The result
There are four excel functions, the HOUR function to extract the hour values, the MINUTE function to extract the minute values, and the SECOND function to extract the second values.
If you want to add n seconds to time then add n in the SECOND function result, then all three values (hour, minute and second) are reassembled using the TIME function to get the new time value.
The results are the same as no excel function solution.
How to add MINUTES to time in Excel?
Simple formula solution
The formula
=A2+(B2/1440)
The result
Excel function solution
The formula
=TIME(HOUR(A2),MINUTE(A2)+B2,SECOND(A2))
The result
Like adding seconds to time, but the added minutes placed in the MINUTE function result.
How to add HOURS to time in Excel?
Simple formula solution
The formula
=A2+(B2/24)
The result
The result is the ##### sign for a time that passes midnight when subtracted. For a time that passes midnight when added Excel will show the correct result, even though it has increased by 1 day.
For the subtraction formula if it passes midnight return a negative number. Excel cannot show the result because the correct result should be 1 minus the negative number.
For this reason, formula correction is needed, if the formula result is negative then add with number 1; otherwise, no correction needed.
The modified formula
=IF(A2+(B2/24)<0,1+(A2+(B2/24)),A2+(B2/24))
The result
No more ##### signs
The Other Alternative
You can use MOD function to get the simpler formula but the same result.
The formula
=MOD(A2+(B2/24),1)
The result
the MOD function change the negative number to the positive number, equivalent to formula =1+negative-value.
Excel function solution
The Formula
=TIME(HOUR(A2)+B2,MINUTE(A2),SECOND(A2))
The result
The result is a #NUM! Error, for time value pass the midnight when subtracted.
The solution is the same as the previous formula; you should check which formula returns a negative number (passes the midnight when subtracted). If there is a negative number, you should add correction by adding 24; otherwise, no correction needed.
The modified formula
=TIME(IF(HOUR(A2)+B2<0,24+HOUR(A2)+B2,HOUR(A2)+B2),MINUTE(A2),SECOND(A2))
The result
The solution for negative time value is the same for hours, minutes or seconds.
No error for minutes and second in the example above because the subtraction result is a positive number (does not pass the midnight)
How to add HOURS, MINUTES, SECONDS to time in Excel?
What is the formula for adding hours, minutes and seconds to a time? Can you use a simple formula or must use an excel function?
Simple formula solution
The formula
=A2+(B2/24)+(C2/1440)+(D2/86400)
The result
The ##### sign appears for negative time value. For this reason, you need to modify the formula to eliminate the errors.
The modified formula
=A2+(B2/24)+(C2/1440)+(D2/86400)+1
The result
The difference with the previous formula is the addition of +1. Why +1 solve the problem?
The problems only appear for negative time value; the solution is to add +1. Whereas +1 for positive time value keep showing the correct result, although the date changes (increases by 1).
The data above does not include the date values; then you can use +1 for positive or negative time value. The result is a simpler formula.
You cannot use the above formula if you include the date value, because the results will be different.
If you include the date, no need for formula correction because the result is the correct time and the correct date.
An error will occur if the returning date is smaller than January 1, 1900.
Excel function solution
The formula
=TIME(HOUR(A2)+B2, MINUTE(A2)+C2, SECOND(A2)+D2)
The result
#NUM! Error appears for negative time value.
The modified formula
=TIME(HOUR(A2)+B2+24, MINUTE(A2)+C2, SECOND(A2)+D2)
The result
No errors found
