Formula to find the Percentage of hours monthly

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
110
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Guy

Am Having an issue around this, i need to find the Percentage of the hours monthly ignoring the negative time just for the time without negative

i Have The Month in Drop Down EJ 16 and i need the monthly result n Column EM 16

ILSP KPI Performance Tracking Master Data-1.xlsx
EJEKELEMENEO
1601-Mar-21Monthly Average
17MonthMus No VslVSL Arrival Jetty Date & Time Supplier Name Arrival Date & Time Delay Hours
1801-May-21114913/05/21 06:30Schlumberger 13/05/21 09:102:40
1901-May-21114913/05/21 06:30Schlumberger 13/05/21 11:104:40
2001-May-21114913/05/21 06:30Uni Arab13/05/21 18:3012:00
2101-May-21114913/05/21 06:30Emdad13/05/21 17:0010:30
2201-May-21114913/05/21 06:30WeatherFord 12/05/21 16:45-13:45
2301-May-21112010/05/21 13:35United Safety 09/05/21 11:00-2:35
2401-May-21112310/05/21 13:15Alphamed09/05/21 09:21-3:54
2501-May-21112510/05/21 21:15Halliburton11/05/21 10:4513:30
2601-May-21112510/05/21 21:15Schlumberger 11/05/21 09:0011:45
2701-May-21112510/05/21 21:15Al Ahlia10/05/21 11:00-10:15
2801-May-21112510/05/21 21:15Alphamed10/05/21 09:00-12:15
2901-May-21112611/05/21 01:05Halliburton10/05/21 12:00-13:5
3001-May-21112611/05/21 01:05Halliburton09/05/21 12:20-12:45
3101-May-21112611/05/21 01:05Averda10/05/21 09:40-15:25
3201-May-21112611/05/21 01:05WeatherFord 10/05/21 16:00-9:5
3301-May-21112711/05/21 07:20WeatherFord 10/05/21 14:30-16:50
3401-May-21112711/05/21 07:20Al Ahlia10/05/21 12:40-18:40
3501-May-21112711/05/21 07:20BJ Service 10/05/21 22:25-8:55
3601-May-21112711/05/21 07:20Sodexo / Kelvin11/05/21 16:108:50
3701-May-21112711/05/21 07:20Selective Marine11/05/21 20:1012:50
3801-May-21112711/05/21 07:20Alphamed10/05/21 09:00-22:20
3901-May-21112711/05/21 07:20Stardford Marine11/05/21 20:5513:35
4001-May-21113011/05/21 17:48AmBhertel LLc11/05/21 20:102:22
4101-May-21113211/05/21 15:30Al Ghaith11/05/21 09:30-6:0
4201-May-21113511/05/21 16:05International Tubular11/05/21 21:505:45
4301-May-21113511/05/21 16:05Al Ahlia11/05/21 16:450:40
4401-May-21113611/05/21 23:05WeatherFord 09/05/21 16:45-6:20
4501-May-21113611/05/21 23:05Alphamed11/05/21 09:30-13:35
4601-May-21113912/05/21 01:00ADNH11/05/21 12:50-12:10
4701-May-21113711/05/21 23:30Al Mansoori10/05/21 14:00-9:30
4801-May-21113711/05/21 23:30Alphamed11/05/21 00:20-23:10
4901-May-21113711/05/21 23:30Halliburton10/05/21 13:15-10:15
5001-May-21113711/05/21 23:30Al Ghaith10/05/21 11:30-12:0
5101-May-21113711/05/21 23:30Scomi11/05/21 12:00-11:30
5201-May-21114012/05/21 09:10WeatherFord 10/05/21 14:30-18:40
5301-May-21114112/05/21 11:00Averda10/05/21 09:40-1:20
5401-May-21114112/05/21 11:00Specialist Services12/05/21 13:102:10
5501-May-21114212/05/21 12:10Mi-Swaco09/05/21 12:10-0:0
5601-May-21114212/05/21 12:10Schlumberger 12/05/21 17:505:40
5701-May-21114312/05/21 13:35ADNOC Driling / IDS12/05/21 08:05-5:30
5801-May-21114312/05/21 13:35Sodexo / Kelvin12/05/21 21:007:25
5901-May-21114712/05/21 18:25ADNOC Driling / IDS12/05/21 21:403:15
ILSP_KPI_Tracking_Data_Entry
Cell Formulas
RangeFormula
EJ18:EJ59EJ18=IFERROR(EOMONTH(EL18,-1)+1,"")
EL18:EL59EL18=IFERROR(VLOOKUP(EK18,$DL$18:$DW$60002,9,0),"")
EO18:EO59EO18=IFERROR(IF(EN18-EL18<0, "-" & TEXT(ABS(EN18-EL18),"h:m"), EN18-EL18),"")
Cells with Data Validation
CellAllowCriteria
EJ16List=$ACT$18:$ACT$209
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,659
Office Version
  1. 365
Platform
  1. Windows
You mention percent but as a percent of what ?

For sum try
Excel Formula:
=SUMIFS($EO$18:$EO$23,$EJ$18:$EJ$23,$EJ$16,$EO$18:$EO$23,">0")

For average:
VBA Code:
=AVERAGEIFS($EO$18:$EO$23,$EJ$18:$EJ$23,$EJ$16,$EO$18:$EO$23,">0")

You can leave out the last criteria if you want to.
$EO$18:$EO$23,">0"
since the negatives are in fact entered as text, both formulas are ignoring them anyway.
 
Solution

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
110
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
HI Alex
Thanks with the formula and sorry for the late reply i was actually not feeling fine so i was unable to read my msg, just came online now

However i was able to try it out and it was ok

Regards
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,659
Office Version
  1. 365
Platform
  1. Windows
Thanks for letting me know. Hope you are feel better soon.
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
110
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Dear Alex

Yes am ok now and back to work

Thanks so much

Regards
 

Forum statistics

Threads
1,141,073
Messages
5,704,138
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top