Excel Time Calculations and Formulas help needed

T

tad_bit_lost

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!
 
P

philbert134

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

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

Re: Need a little help please with excel.

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

Hodgepodge

Re: Need a little help please with excel.

See the attached spreadsheet for an explanation on formatting cells in the Time format. If you have any questions, just ask.
 

Attachments

  • Time Log Example.xlsx
    9.8 KB · Views: 283
  • Time Log Example Excel 97-2003.xls
    19 KB · Views: 324
A

ab001

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

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

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

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

Trusted Information Resource
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
 
Top Bottom