• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Business Hour Calculation

· We are working in three shifts for EUR, North Americas and Brisbane starting 11:30 AM in the morning till 8:30 PM, second shift starts at 5:30 PM to 2:30 AM and the last shift for the day is 4:30 AM to 1:30 PM next day.

· If you see we have overlap 3 hrs. in the first shift and second shift and last shift and first shift.

· We have two hours of SLA for each PR or PO to get approved in that particular shift.

· So if there are any PRs or POs that we receive from EUR site post 8:30 and gets approved at 11:30 ideally it’s not a SLA miss as this is we are approving even before the shift is starting hence it should be 0 hours and this applies to all the shifts. But in my current sheet u will see we have SLA’s which shows as 8 hrs 14 hrs and 16 hrs ideally I have not missed my SLA but this will show as SLA miss.

· We work from Monday to Friday.
 

Attachments

  • Sample data.xlsx
    16.6 KB · Views: 4
You imply you are needing some type of solution that crosses multiple time zones but you failed to ask a question and mention what time zones you are using. Also, some may not know what all your abbreviations are, like: SLA, PL, PR. Might any of them be?
Service Level Agreement
Sealed Lead Acid
Second Language Acquisition
Special Libraries Association
Statistical Local Area
Symbionese Liberation Army
Software License Agreement
Shift Left Arithmetic
Supported Living Arrangement
Surplus Line Association
Single Living Accommodation
Structured Learning Assistance
Student Leadership Association
Subsidiary Loan Agreement
Stout Londan Ale
Site Location Assistance
Strategic Logistics Agency
Salta, SA, Argentina
Society for Location Analysis
Sample Log Analysis
Stereo Lithography Apparatus
Service Level Agreement
Sealed Lead Acid
Second Language Acquisition
Special Libraries Association
Statistical Local Area
Symbionese Liberation Army
Software License Agreement
Shift Left Arithmetic
Supported Living Arrangement
Surplus Line Association
Single Living Accommodation
Structured Learning Assistance
Student Leadership Association
Subsidiary Loan Agreement
Stout Londan Ale
Site Location Assistance
Strategic Logistics Agency
Salta, SA, Argentina
Society for Location Analysis
Sample Log Analysis
Stereo Lithography Apparatus

Public Relations
Puerto Rico (US postal abbreviation)
Press Release
Purchase Request
Progress Report
Permanent Resident
Pattern Recognition
Performance Review(s)
Problem Report
Personal Record
Project Report
Punk Rock
Preston (postcode, United Kingdom)
Prairie (street suffix)
Payroll
Prices Realized
Peer Review
Power Rating (gaming)
Primary Responsibility
Probability
Progesterone Receptor
Power Rangers (cartoon)
Personal Reesentative
Price Reduction
Preview Release (a post-beta software ogram)
Princeton Review
Proportional Reesentation
Performance Rating (ocessors)
Pro Rata (Latin: in oportion to)
Philippine Airlines
Problem Resolution
Pulse Rate
Presidência da República (Portugal)
Prayer Request
Pseudo Random
Propyl
Peoples' Republic
Presidente da República (esident of the Republic)
Pine-Richland (Pennsylvania school district)
Presidente da República (Portuguese esident)
Presidente da República (Portuguese: esident of the Republic)
Pregnancy Rate
Precipitation Radar
Praseodymium
Point Roberts (Washington)
Process Reengineering (Sint)
Program Register
Pokemon Ranger
Punt Returner (football)
Pinar del Rio (postcode, Cuba)
Pulmonary Rehabilitation
Personal Radio
Plymouth Rock (chicken breed)
Program Resource
Prevalence Rate
Preliminary Review
Porter Ranch (California)
Purchase Requisition
Policy Region
Product Request
Phantom Regiment (Drum and Bugle Corps, Rockford, Illinois)
Prevalence Ratio
Pax Romana (Latin: Roman Peace)
Petit Robert (French dictionary)
Phosphate Rock
Prandtl Number
Projekt Revolution (Linkin Park tour)
Per Rectum (method of medication)
Pharmaceutical Reesentative (journal)
Pesticide Registration (EPA)
Priority Records (record label)
Poisson's Ratio
Pour Remercier (French: to exess thanks)
Procurement Regulations
Percentile Rank
Parma, Emilia (Italian ovince)
Personal Recognizance (bond/bail)
Personnel Recovery
Performance Run (fitness)
pump room (tankers)
Pseudorabies
Received Power
Process Reliability
Procurement Request
Portfolio Rebalancing
Pilot Run
Policy Record
Photo Resist
Photochemical Reaction
Position Reference
Project Revolution (gaming, Warcraft IIITotal Conversion)
Product Record
Periodic Reinvestigation
Pershing Rifles
Per Rectal (examination of a patient)
Position Release (AT&T)
Pseudo-Range
Psychopathic Records (CD label)
Pressure Recorder
Photo-Recon
Purchaser's Right
Prosthion
Prothrombin Ratio
Pulmonic Regurgitation
Punjab Regiment (Pakistan)
Posting Reference
Processor Rating
Pesticide Regulation Notice (EPA)
Pyrolytic Release Experiment
Pneumatic Ram
Polarization Rotator
Punctum Remotum (Latin: far point of accommodation)
Photo Reflectance
Planned Requirement
Pusher Robot (gaming)
Planet Replay (media/electronics store)
Preventive Replacement
Potomac Runners (Washington, DC area running group)
Parameter Redundancy
Procedural Reflection
Profundo Rosso (movie)
Pitch Runner (baseball)
Parti Républicaine (French: Republican Party)
Path Restorable
Parti Republikan (Republican Party; political party, Albania)
Procurement/Purchase Request
Ponti Radio SpA (Italy)
Prototyping Request
Power Repeatability (Agilent)
Periodogram Roughness (economics)
Personal Reschedule
Packet Radio
Passive Ranging
Parachute Rigger (USN Rating; obsolete)
Paraná (Brazilian state)
Partial Remission (oncology)
Pathogenesis-Related (oteins)
Patent Rules
Part Request
Parks and Recreation
Pakistan Railways
Partial Reconfiguration
Paris
Papa Roach (band)
Partial Resistance
Paper Round
Patrick Roy (former hockey goalie)
Pastor
Park Ridge
Partial (METAR descriptor)
PageRank (Google search technology)
 
Hi Eloise,

We are working in three time zones that EST, CET and Australian time zone. SLA is service line agreement.

Purchase Order (PO)
Purchase Requisition (PR)

The solution that i am looking forward is if we received any PR or PO is received after EST shift timing i.e after 5 PM EST and its gets approved by the Australian shift team which is ideally mid night in EST. We are approving the PR way ahead of our shift start so ideally the SLA should be 0 hours.

Please advise
 
Consultant.preeti

Can I please direct you to the site rules located at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/

It is important to understand that although your problem maybe important to you, it has no importance to anyone else here.

Please note that words in the posts title like UTMOST Urgent, pretty well get ignored and in fact may lessen your chance of getting a quick response.

This forum is serviced by Volunteers. As a Volunteer we receive no remuneration. And as Volunteers we get the right to choose posts that are of interest to us or not.

If you want to sub-contract an Excel Consultant to assist you can be more demanding of them.
 
With regards your question:

It is unclear what you are after

Using your PR Data

upload_2016-6-9_8-10-20.png

I can see the SLA is 3:12 but that is 2 days later, 3 June to 5 June

Can you please clarify what the actual requirement is, possibly giving an example
 
I would think something like this?
I filtered those SLA miss rows.
(from 03/06/2016 18:15 to 05/06/2016 21:27 is 51:12)
 

Attachments

  • Sample data.xlsx
    18.6 KB · Views: 10
Back
Top