Microsoft Excel querry

[sunilbt]

Hi Excel expert,

I have been struggling with a querry in the excel sheet.

As shown in the below excel picture. I have been trying to calculate the time taken between column D and Column C.And I want the difference in "number of days" "Hours" and "minutes".


If see below in the column 'E" is it is showing me as "1 day" "0" hours and "57" minutes. Rather it should show me as "0" days "0" hours and "57" mintutes.

C D E

<table border="0" cellpadding="0" cellspacing="0" width="350"><col style="width: 96pt;" width="128"> <col style="width: 81pt;" width="108"> <col style="width: 86pt;" width="114"> <tbody><tr style="height: 54pt;" height="72"> <td class="xl71" style="height: 54pt; width: 96pt;" width="128" height="72">CORR compile started</td> <td class="xl71" style="border-left: medium none; width: 81pt;" width="108">CORR delivered</td> <td class="xl71" style="border-left: medium none; width: 86pt;" width="114">CORR creation time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17"> </td> <td class="xl69" style="border-left: medium none;"> </td> <td class="xl70" style="border-left: medium none;"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl68" style="height: 13.5pt;" height="18"> </td> <td class="xl68" style="border-left: medium none;"> </td> <td class="xl70" style="border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt;" height="17">4/24/2010 10:30</td> <td class="xl72" style="border-left: medium none;">4/24/2010 11:27</td> <td class="xl73" style="border-left: medium none;">01 00:57</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">5/1/2010 10:30</td> <td class="xl68" style="border-left: medium none;">5/1/2010 11:21</td> <td class="xl70" style="border-left: medium none;">01 00:51</td> </tr> </tbody></table>

Could you please help mw with the right formula?

Regards,
Sunil.

 

[DauntingGecko]

You need to upload your screenshots to a website. You can't do it from your PC.

Google "free image hosting"

 

[jim may]

This maybe?

Excel Workbook
	A	B	C	D	E
1	4/24/2010 10:30	4/24/2010 11:27	0Hours :57minutes :00seconds		<< Format Cell - Custom: [h]"Hours" :mm"minutes" :ss"seconds"
2	5/1/2010 10:30	5/1/2010 11:21	0Hours :51minutes :00seconds
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C1	=B1-A1

 

[jim may]

Wrong!!

 

[jim may]

Crude, but this will get you there -- I'm SURE there is a better way, unknown to me at the time... LOL - so this is FWIW.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start Time	End Time	Hours	Minutes	Days	Hours	Minutes	Concatenated
2	4/24/11 10:30 AM	4/24/11 11:27 AM	0	57	0.00	0.00	57.00	0 Days, 0 hours and 57 minutes
3	5/1/11 10:30 AM	5/1/11 11:21 AM	0	51	0.00	0.00	51.00	0 Days, 0 hours and 51 minutes
4			******** YOU COULD HIDE COLUMNS C - G ********
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=INT((B2-A2)*24)
D2	=((B2-A2)*24-C2)*60
E2	=INT(C2/24)
F2	=C2-(E2*24)
G2	=ROUND(D2,0)
H2	=E2 & " Days, " & F2 & " hours and " &G2 & " minutes"

 

[sunilbt]

Crude, but this will get you there -- I'm SURE there is a better way, unknown to me at the time... LOL - so this is FWIW.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start Time	End Time	Hours	Minutes	Days	Hours	Minutes	Concatenated
2	4/24/11 10:30 AM	4/24/11 11:27 AM	0	57	0.00	0.00	57.00	0 Days, 0 hours and 57 minutes
3	5/1/11 10:30 AM	5/1/11 11:21 AM	0	51	0.00	0.00	51.00	0 Days, 0 hours and 51 minutes
4			******** YOU COULD HIDE COLUMNS C - G ********
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=INT((B2-A2)*24)
D2	=((B2-A2)*24-C2)*60
E2	=INT(C2/24)
F2	=C2-(E2*24)
G2	=ROUND(D2,0)
H2	=E2 & " Days, " & F2 & " hours and " &G2 & " minutes"

Hi,

Thanks For your trick.This works if there is only few vlaues.
If i hide the columns C-G.. How about for next values.Do i need to make manual entry of these formulas everytime i have new values in Column A and Column B?

I tried hiding the columns C-G and then just put the values for A4 and B4.But I didnt get the desired output in the column H4.

Regards,
Sunil.

 

[sunilbt]

Hi,

Thanks For your trick.This works if there is only few vlaues.
If i hide the columns C-G.. How about for next values.Do i need to make manual entry of these formulas everytime i have new values in Column A and Column B?

I tried hiding the columns C-G and then just put the values for A4 and B4.But I didnt get the desired output in the column H4.

Regards,
Sunil.

 

[jim may]

You would paste in your dates/times to Column A and Column B

the formulas in Columns C - H would be copied down as far as you have data in Columns A & B

 

[sunilbt]

Hi Jim,

I am not getting right values for all the time stamps ..I would be more happy to get one formula instead of multiple formulas.I really appreciate your support..

Regards,
Sunil.

 

[jim may]

Substituting the individual formulas into the lead formula you get:

=INT(C2/24) & " Days, " & C2-(E2*24) & " hours and " &ROUND(D2,0) & " minutes"

that's all I can offer!!