Calculating time difference between two date/times from two columns in Excel

Question

I have 5 columns: Start Date, Start Time, End Date, End Time and Duration. I need to calculate the duration between the Start Date/Start Time and the End Date/End Time. I tried just subtracting the end time from the start time, but I am having issues when it spans two days. I haven't been able to find a solution that doesn't require me to add two new columns (Start Date/Time and End Date/Time). Any ideas?

Solution

I have found the errors in my way. I had to do the following:

• Format Start Date/End Date as mm/dd/yyyy
• Format Start Time/End Time as HH:MM:SS (24 hr format***)
• Then I was able to use the formula (in R2) =(P2+Q2) - (N2+O2)

Comments

[Ƭᴇcʜιᴇ007]

You say you found some solutions already. What were they? Bascially, what have you tried already, so we don't give answers you've already researched and found unacceptable. Are you looking to do this only in formulas, or have you also been trying VBA/macros?

[FancyPanda]

I'm trying to do this with formulas only. I think I found my issue.. when I was trying to do N2 + O2 (Start Date + Start Time) it wouldn't work because the date in N2 was not formatted correctly.

[CharlieRB]

possible duplicate of Calculating a duration in excel from date and time

[FancyPanda]

@CharlieRB Unfortunately, the two posted solutions have not worked for me. I would like to see the format in HH:MM:SS, not just total number of minutes.

[FancyPanda]

Actually, I shouldn't have doubted myself, the math is correct!

[CharlieRB]

You are right. I was looking at it as hours and minutes, but they are certainly in the minutes and seconds place. I will remove my comment. Sorry about that.

[FancyPanda]

@CharlieRB No problem! You had me convinced at first :)