Hello,
Introduced to this forum by the great Excel-guru Mr. Ashish Mathur. ( Thanks Ashish, for your cooperation ). I use Excel for my MIS job in Telecom field in order to generate REPORTS and REPORTS. Hence at times I really need somebody to help me doing the same things in an even easier way if I cannot find a solution by myself.

Presently, I am stuck with a time-calculation problem. Let me summarize:

I have 4 columns containing "Start Date", "Start Time", "End Date" and "End Time" respectively.
In the 5th column, I need to calculate the time difference in terms of "Hours and Minutes" in the format [h]:mm
e.g., in the following table, I need a "1-cell formula" to get the value "35:50"

Please help.

Thank you very much. That solves my problem easily. Its like magic.

Ashish Mathur wrote Hi, Assuming the data above is entered in range A12, enter the following formula in cell E2 to get the time difference. =IF(B2>=D2,TEXT(12-(B2-D2),"hh:mm:ss")+(C2-IF(B2=D2,0,1))-A2,TEXT(D2-B2,"hh:mm:ss")+(C2-A2)). In Format>Cells>Number.Custom, please enter the following time format in the Type box: [h]:mm:ss Hope this solves your problem.

Hi Ashish,

As I was working with your formula, a sudden idea flashed in my mind, and yes, it worked. Now, I have been able to simplify the formula farther. Here it goes:

=TEXT((C2+D2)-(A2+B2),"[h]:mm:ss")

You know, we were thinking of complicated methods. In fact, Excel does not differentiate between date and time, both are treated as numbers, hence simple addition and subtraction is possible.

I wish we thought of it earlier. Anyway, better late than never.

-Argha Bagchi

http://www.linkedin.com/in/argha