Excel Time Calculations and Formulas help needed

T

tad_bit_lost

#1
I am new to the forum so please forgive me if I am out of step.

I am trying to set up a spreadsheet that will allow me to enter a start time and end time then give me the total minutes that a patient waited in the waiting room.

If you are able to help thanks! If not thanks anyways! :)

TIA and have a good day!
 
Elsmar Forum Sponsor
P

philbert134

#2
Re: Need a little help please with excel.

If you format the entry cells and the output cell as "time" with the "13:30:55" option selected and enter the equation "=B1-A1" in the output cell (assuming B1 and A1 are your discharge and admission times, respectively), it should work. All times are 24hr format and the equation returns a value of HH:MM:SS. Do you need instructions on cell formatting?
 
P

philbert134

#3
Re: Need a little help please with excel.

I should have said B1 and A1 are the cells containing your discharge and admission times.
 
T

tad_bit_lost

#4
Re: Need a little help please with excel.

Thanks for your answer. If you can advise me on it I would appreciate it!
 
A

ab001

#6
Re: Need a little help please with excel.

i found it so liberating when i figured out how excel handles times and dates:
20July2010 is 40,379 days since the nominal start day 01Jan1901 (or something).

8am is just a fraction of that day (40379.25)
1pm is just a fraction of that day (40379.541666667)

so just do your calc in whole days, then use formatting to present the answer properly. (see hodgepodge's examples)

my only warning would be if you have to convert to something else (cost), remember that you are using days as a calculating unit, so your conversion factor would be dollars per day.
 
S

staftrax

#7
Hi,
I also struggled for a while with the problem of how to calculate elasped time in excel, my problem was slightly more complex in that, I also wanted to span across the midnight hour.
This example has the formula do what you want.

B3 = Tme in
C3 = Time out
D3 = The result in elasped time in hours and minutes
=IF(MOD(C3-B3,1)>MOD(B3-C3,1),MOD(B3-C3,1),MOD(C3-B3,1))



Good luck
 
Last edited by a moderator:
H

Hodgepodge

#8
Hi,
I also struggled for a while with the problem of how to calculate elasped time in excel, my problem was slightly more complex in that, I also wanted to span across the midnight hour.
This example has the formula do what you want.

B3 = Tme in
C3 = Time out
D3 = The result in elasped time in hours and minutes
=IF(MOD(C3-B3,1)>MOD(B3-C3,1),MOD(B3-C3,1),MOD(C3-B3,1))



Good luck
staftrax,
I tried the formula you offered, but was unable to get the correct answer spanning midnight. Perhaps the formula below would be more appropriate.

=IF(MOD(C3-B3,1)>MOD(B3-C3,1),MOD(24-B3,1)+MOD(C3,1),MOD(C3-B3,1))
 
S

staftrax

#9
Hi Hodgepodge
Tried both formulas and they both give the same result.
I wound send you the excel but I'm new to the forum, I've yet to work out how to upload an attachment
Cheers
 

Tim Folkerts

Super Moderator
#10
The other obvious solution is to also include the date with the time. If the date is in column A and time in column B, then the formula would be

(A2+B2) - (A1+B1).



Tim
 
Thread starter Similar threads Forum Replies Date
B How to validate Microsoft Excel Spreadsheet Calculations for the Cpk value Capability, Accuracy and Stability - Processes, Machines, etc. 9
AnaMariaVR2 Guide to Microsoft Excel for Calculations, Statistics, and Plotting Data Excel .xls Spreadsheet Templates and Tools 11
M Capacity Analysis or Calculations - Excel Spreadsheet needed Excel .xls Spreadsheet Templates and Tools 3
I Gage R&R Form - Excel or other - Calculations Questions Excel .xls Spreadsheet Templates and Tools 13
J Incoming Inspection Records using Excel File ISO 13485:2016 - Medical Device Quality Management Systems 2
S 95% UCL - Attaching an excel for recovery study Statistical Analysis Tools, Techniques and SPC 0
T EXCEL - How do I make the cell stay blank until a date is entered in N7? Excel .xls Spreadsheet Templates and Tools 6
Ed Panek Validating Excel (XLS) formulas 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 7
C Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
R Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
S Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Ajit Basrur Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 9
J Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
P Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
I Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23
M Matching two matrices in Excel Excel .xls Spreadsheet Templates and Tools 5
J Level 3 KPI Excel Template Manufacturing and Related Processes 1
bobdoering Informational New AIAG PFMEA Process - Excel Template Attached FMEA and Control Plans 23
GreatNate Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
E Part 11 Compliance, Excel living documents (i.e. document master list, equipment list, approved supplier list) Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 3
J ISO 9001:2015 Excel Gap Analysis wanted ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
B AS9110 Audit Check Sheet wanted Excel or Word Document AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
G Excel report with pictures Excel .xls Spreadsheet Templates and Tools 5
A Query regarding MS Excel usage Excel .xls Spreadsheet Templates and Tools 2
A Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3
D Asterix when pasting a column of values from Excel into Minitab (V17) Using Minitab Software 5
Y Excel Automation - Printing Word Documents from within Excel Excel .xls Spreadsheet Templates and Tools 0
S Help me build an Excel Spreadsheet for tracking training Training - Internal, External, Online and Distance Learning 14
P Template or example Excel list of ISO certificates issued to clients ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
M Medical Device Process validation, Validation of excel spreadsheets used for process Other Medical Device and Orthopedic Related Topics 4
C Excel date format from Denmark to USA Excel .xls Spreadsheet Templates and Tools 6
G ISO 9001:2015 - Is control possible with Excel adaptable measurement sheets? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 24
N Where I can find an Excel sample/template for recording Delivery Time and Cost Excel .xls Spreadsheet Templates and Tools 2
D How do I go about creating document logs and registers with the MS Excel Excel .xls Spreadsheet Templates and Tools 2
D Microsoft Excel database to Stand-alone software Calibration and Metrology Software and Hardware 3
Q Controlling Multi-Tab Microsoft Excel Forms Excel .xls Spreadsheet Templates and Tools 5
B Zeiss CMM Calypso output to Excel or CSV file Help Inspection, Prints (Drawings), Testing, Sampling and Related Topics 17
S used excel formula calculated the ARL's with Rule1&Rule2 for Shewhart control chart Statistical Analysis Tools, Techniques and SPC 0
N Gantt Chart / Time Tracker in Excel - Combining Tasks on the Same Line Excel .xls Spreadsheet Templates and Tools 2
M Preventive Maintenance Spreadsheet in Excel Quality Assurance and Compliance Software Tools and Solutions 2
1 KPI Dashboard Template in Excel Format wanted Excel .xls Spreadsheet Templates and Tools 9
Ajit Basrur MS Excel to study Correlation between Humidity & Differential Pressure Excel .xls Spreadsheet Templates and Tools 7
D Gage Calibration Tracking in MS Excel Calibration and Metrology Software and Hardware 9
T Where can I get a PPAP workbook in Excel .xls with links? APQP and PPAP 1
L Excel Template for Cgk Study wanted (Capability Gauges Study) Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 12
V Looking for advice on Pooled Standard Deviation between Minitab and MS Excel Statistical Analysis Tools, Techniques and SPC 1
P Recommendations on SPC Software or Excel SPC add-in(s) Quality Assurance and Compliance Software Tools and Solutions 6
T Calculating LCL, UCL, Cp, and Cpk in an Excel Spreadsheet Statistical Analysis Tools, Techniques and SPC 3
R Is there any Excel .xls Spreadsheet available for DOE with 3 factors? Six Sigma 1

Similar threads

Top Bottom