Adding time

Topic

New Reply

I need to add/subtract start times and end times together to come up with a duration. For instance if I enter 11PM in one cell and 1AM in the next cell, can Excel (with a formula) tell me that I have spent 2 hours on a certain project?

Thanks in advance for your help.

Christa

Reply | Quote

Replies

Excel doesn’t handle negative times, so subtracting one time from another won’t work. I think the easiest way is to use an IF function. If your times (without a date) are in A1 and B1, then

=IF(A1>B1,B1+1-A1,B1-A1)

subtracts and finds the duration.

The “1” in the “true” section of this formula allows for B1 to be in the next day. Note, without formatting this result, you get a decimal that is the fraction part of a 24 hour day. You could either format the result of multiple it by 24, depending on whether you want “2” or “2:00”.

HTH

Reply | Quote

Thanks, I’ll give that a try

Reply | Quote

What is really in those cells? If the cells contain date and time (ie. you entered “4/19/2001 11:00 pm”), then you should be able to just subtract the start from the finish and come up with the elapsed time. You can then format the result with the format [hh]:mm with the btrackets in the format to get the time spent on the project. However, if the cells contain just the time (you just entered “11:00 pm”), then you are going to have to use a formula with an If command to adjust the finish time if it is in the next day.

Reply | Quote