Time sum help

[Nabeel]

hello all
plz find attached plz sum the time and i have apply conditional formatting for duplicate values now i want to remove whole row of duplicate how i can do it plz guide also..
BR

 

[jayalaxmi]

see if this works for u

 

[Deepak]

Check this in V14
CSE

=SUM(IF(LEN(D14:U14)>0,LEFT(D14:U14,3),0)*1)+SUM(IF(LEN(D14:U14)>0,RIGHT(D14:U14,2),0)*1)/60

 

[Nabeel]

not working..

 

[eibi]

Hi Nabeel,

If the time values you provided are Minutes:Seconds, you can paste this formula in V14:

=SUM(IF(ISBLANK(D14:U14),0,TIME(--LEFT(D14:U14,FIND(":",D14:U14)-1),--RIGHT(D14:U14,2),)))

If the time values you provided are Hours:Minutes, you can paste this formula in V14:

=SUM(IF(ISBLANK(D14:U14),0,TIME(,--LEFT(D14:U14,FIND(":",D14:U14)-1),--RIGHT(D14:U14,2))))

Note, both are array formulas, confirmed with CTRL + SHIFT + ENTER.

See attached.

 

[Somendra Misra]

Hi Nabeel,

Your time are text values and not proper time. If you can convert your data to proper data than you can create a helper column with a formula like:
=IF(COUNTIF(B$14:B14,B14)=1,1,0) in W14 and copy down

which will identify the first occurance of an employee, than in Total column you can use formula like =IF(W14, SUM(time values),"")

Regards,

 

[Deepak]

not working..

You might not entered it with Ctrl+shift+enter.
Or share the expected result.

 

[Nabeel]

Hi Nabeel,

If the time values you provided are Minutes:Seconds, you can paste this formula in V14:

=SUM(IF(ISBLANK(D14:U14),0,TIME(--LEFT(D14:U14,FIND(":",D14:U14)-1),--RIGHT(D14:U14,2),)))

If the time values you provided are Hours:Minutes, you can paste this formula in V14:

=SUM(IF(ISBLANK(D14:U14),0,TIME(,--LEFT(D14:U14,FIND(":",D14:U14)-1),--RIGHT(D14:U14,2))))

Note, both are array formulas, confirmed with CTRL + SHIFT + ENTER.

See attached.

thnx dear eibi its working

 

[Nabeel]

hello there

second part of my question, is this possible..in column c i have a conditional format for duplicate values..i want to delete entire row of duplicate..how i can do it.plz guide

 

[eibi]

Righto.

So just to make sure we're on the same page -- how will excel know which of the duplicates to ignore?

Do you want to keep the duplicate with the highest time value? Or the one nearest the top of the list?

For example -- Nabeel Akhtar Gondal appears on line 30 and line 32. Which occurrence do you want to keep, and which do you want to ignore?

Also, which time format are we working with? HH:MM or MM:SS?

Thanks.

 

[Somendra Misra]

Select Entire Data Say from A14:V30, goto Data Tab, select remove duplicate now select column in which you want to see duplicates, say in your sample file column B. Press OK.

Regards,

 

[Nabeel]

thnx once again

 

[Nabeel]

hello dear Mr eibi
plz find attached for your kind help...in whole column the formula is working but at last column its not working..plz put some changes in formula if u can or any other formula..because it is short data for discussing
BR