How to change hour format to text but not changing the number ?

Berger

Starting to get Involved
#1
Hi All,
I try to find the way to change the hour format to text but not changing the number
Example
10:30 ( format as 10:30:00 AM) ==> Want to change to 10.30
225:00 ( format 1/9/1900 9:00:00 AM) ==> want to change to 225.00

The 10:30 and 225:00 are should not be hours format but equal to how many hour and minutes
10:30 = 10 hours and 30 mins
225:00 = 225 hours

The reason is I want to use pivot table but the result (sum) is incorrect as it represent hour
Please see Book1 attached for more explanation.

Thank you in advance for your help :)
 

Attachments

Elsmar Forum Sponsor
#2
It seems like your formula to sum up all of the yellow times is accurate. The sum of column D (green) is not because 30 minutes doesn't equal 3/10ths of an hour. 30 Minutes is actually 5/10ths of an hour, so a time of 10:30 would be translated to 10.5 in your example.

This article talks about the correct custom format to be able to sum quantities of time (Column B) in your sheet. It appears they are already formatted correctly.

How to Add, or Sum, Time Values in Excel
 
Thread starter Similar threads Forum Replies Date
C 1 hour minimum change over to 3 minute change over, (still in testing) Lean in Manufacturing and Service Industries 8
JoCam Change of Legal Manufacturer EU Medical Device Regulations 0
Sidney Vianna Informational ISO/TS 10020:2022 Quality management systems — Organizational change management — Processes Other ISO and International Standards and European Regulations 0
0 Complaints time series significant change Customer Complaints 41
N Malaysia Shelf Life Change Other Medical Device Regulations World-Wide 2
P Self Certified Class 1 MDR 2017/745 Design Change CE Marking (Conformité Européene) / CB Scheme 2
O Article 120 - significant change interpretation EU Medical Device Regulations 4
V Would the customer drawing change necessitate re-PPAP of the Product? APQP and PPAP 3
S Do we have to create a new PPAP to our customer if our Sub Contractor had to change? APQP and PPAP 10
M PMA submission change Medical Device and FDA Regulations and Standards News 0
U Change to the manufacturing Date for a device in the field US Medical Device Regulations 0
F Change in address for critical supplier - does NB need to be informed? CE Marking (Conformité Européene) / CB Scheme 20
K Change in address on ERLM US FDA: Implications to import US Medical Device Regulations 0
N Change our Registrar? Pros and Cons ? Registrars and Notified Bodies 8
F Using non-randomized clinical study for change in existing 510(k) device? US Food and Drug Administration (FDA) 1
FuzzyD ISO 13485 change in process ISO 13485:2016 - Medical Device Quality Management Systems 3
R IVD Label Change China Medical Device Regulations 1
Judy Abbott Change of PVC stabilizer Japan Medical Device Regulations 6
R IVD Label Change Requirements Other Medical Device Regulations World-Wide 0
M EE's - Laptops/Printer Change and Emissions Testing IEC 60601 - Medical Electrical Equipment Safety Standards Series 6
D Document Change Approval ISO 13485:2016 - Medical Device Quality Management Systems 6
mtmt70a Change\ my MTBF Prediction SW Tool from the RELEX tool with yearly Lic. access Telecordia database to another solution. Recommendations? Reliability Analysis - Predictions, Testing and Standards 0
S Can a product option change the product class? CE Marking (Conformité Européene) / CB Scheme 2
M Regulatory Change assessment US Food and Drug Administration (FDA) 3
G Software change management Design and Development of Products and Processes 3
J Changes to OS - Significant change under MDR EU Medical Device Regulations 8
A If document coding is change will there be a revision Document Control Systems, Procedures, Forms and Templates 1
G Change Control for Test Samples Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
Z Change color or shape of individual data point in control chart Using Minitab Software 6
J Medical Device Component Change - Testing, Sampling Criteria ISO 14971 - Medical Device Risk Management 3
S When to change the status of a GTIN from "Premarket" to "In Use" ? US Medical Device Regulations 2
Y Is there any EU Significant change guidance issued? EU Medical Device Regulations 5
N API Q1, Management of Change & Document Change Request Oil and Gas Industry Standards and Regulations 4
adir88 Substantial Change to QMS (Annex IX, Chapter I) EU Medical Device Regulations 0
A Eudamed actor registrations if you change AR for MDR product EU Medical Device Regulations 0
C Change Management Process ISO 14971 - Medical Device Risk Management 8
S ISO13485:2016 7.3.9 design and development change ISO 13485:2016 - Medical Device Quality Management Systems 3
N Change Control for processes & products Manufacturing and Related Processes 7
Quality Specialist Change Control Numbering system Document Control Systems, Procedures, Forms and Templates 7
D Laboratories listed in the Declaration of Conformity change Canada Medical Device Regulations 1
M Change control on Tracking Sheet ISO 13485:2016 - Medical Device Quality Management Systems 11
D EU MDR - Change of device name (legacy device) EU Medical Device Regulations 17
L Change Log in (controlled) Forms? (ISO 9001:2015) Document Control Systems, Procedures, Forms and Templates 6
H Control of Change i.e. process and WI IATF 16949 - Automotive Quality Systems Standard 9
dgrainger Informational MHRA's Software and AI as a Medical Device Change Programme UK Medical Device Regulations 0
K Is implementing Electronic Signatures a Significant Change to the QMS? ISO 13485:2016 - Medical Device Quality Management Systems 10
A Management of change procedure for ISO 45001 Occupational Health & Safety Management Standards 5
N Cleanroom Classification Change Other Medical Device Related Standards 7
D Engineering Change order vs. Change Control ISO 13485:2016 - Medical Device Quality Management Systems 3
Watchcat Change Request Documents - What's Typical These Days? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 14

Similar threads

Top Bottom