Difference Between Two Times If a Time Exists in a Certain Column

Hi,

This is my first post here. Firstly I should say that I am an Excel novice. In the past I've dabbled in Excel using small amounts of data, but nothing on the scale that I need for this project.

I am a Nurse in a local hospital and I have been tasked with auditing the time that patients spend in our clinic, from checking in, through a variety of tasks and finally seeing the Dr.

I have uploaded a sample of my data:
Patient Flow Audit Sample.xlsx

Each clinic session that I am auditing will have its own spreadsheet that is the same as this sample. The only difference will be the number of lines of data as this depends on the number of patients in that session. Although I am considering setting up each sheet to have 25 lines (which is more than the max number of patients per clinic) to make them look the same and aid copying the formula for each new sheet of data.

I am looking for help with formula's that would populate columns M, N, O and P.

Column M:
I need to ascertain the time between Column E and Column C but only if there is a time in Column E.

Column N:
I need to ascertain the time time between Column F and Column C but only if there is no time in Column E.

Column O:
I need to ascertain the time between Column F and Column E but only if there is a time in both Column F and Column E.

Column P:
I need to ascertain the time between Column G and the latest time in Column C, D, E or F.

I have spent hours tinkering with different formulas with no success and lots of ###### values. I would appreciate any help anyone can give me. I have tried searching the internet for help but I simply don't know what sort of formula to even search for.

Thanks for reading.

See attached. You get the ##### because you can't have negative time.

this is how i'd do the first three, col m, n and o.
formula in m =IF(E2<>"",E2-C2,"")
formula in n =IF(E2="",F2-C2,"")
formula in o =IF(AND(E2<>"",F2<>""),F2-E2,"")
then for p i'd likely use a helper column where I'd go for a max elsewhere (maybe in col v), =MAX(C2:F2)
then do the difference between the max and col G value.

EDIT: Alan's is a cleaner way then my last choice.

Please find the attached sheet to see if this is what you want.

Try adding to your cell in N2 =IF(AND(E2="",F2<>""),F2-C2,"")
that will get rid of the #####.

Change formula in J to this =IF(B2<A2,"",B2-A2).
and change formula in K to this =IF(A2>B2,A2-B2,"")

Glad if that helped you. I'm an RN and I do a lot of data crunching and this forum is great for learning new tricks. Stop by anytime.
And if this issue is solved for you don't forget to mark your post solved using the thread tools at the top and finally, you can thank all who've helped you by clicking on add reputation for them.

EDIT: and thanks for the rep points!