# Calculating number of patients by arrival time (hr)

1. ## Calculating number of patients by arrival time (hr)

I am trying to produce a report to show when patients arrived by the hour?

Arrival Time # patients
7a 2
8a 4
9a 3
10a 4
Etc.

The attached file is what I have been given to work with. HELP!

2. ## Re: Calculating number of patients by arrival time (hr)

I guess I could have provided a little more information. Sorry! I'm new to this forum and could really use your help.

Column J is the date in
Column L is the time in
Column O is the date out
Column R is the time out

3. ## Re: Calculating number of patients by arrival time (hr)

Please explain the data (what is represents) that is available in the attached spreadsheet.

4. ## Re: Calculating number of patients by arrival time (hr)

Sorry, I've labeled columns. Thanks so much for looking at it. I'm normally fairly good with excel, but I'm stumped with this one.

5. ## Re: Calculating number of patients by arrival time (hr)

Column J has Date In
Column L has Time In
Column N has Date Out
Column Q has Time Out

Please copy paste the below provided formula to get the desired result.
For Date In: =DATE("2013",LEFT(J2,2),MID(J2,3,2))
For Time In: =TIME(LEFT(L2,2),RIGHT(L2,2),"00")
For Date Out: =DATE("2013",LEFT(N2,2),MID(N2,3,2))
For Time Out: =TIME(LEFT(Q2,2),RIGHT(Q2,2),"00")

You will get the result for row 2 in the following below format using the above provided formula.

Date In Time In Date Out Time Out
5/1/2013 1:20 AM 5/1/2013 4:48 AM

I have also attached the file with the formula inserted in the column T,U,V and W.

Let me know if you were able to get them.

Thanks,
Wesley

6. ## Re: Calculating number of patients by arrival time (hr)

OMG - lifesaver. I would NEVER have been able to do that. Now.....can you please help me understand what the formula is for telling me that from 1am - 2am, X number of patients arrived, and from 2:01am - 2:59am, x number of patients arrived. By date? I really appreciate your help! Amazing forum!

7. ## Re: Calculating number of patients by arrival time (hr)

Hi

Not sure what you require, but if you put in say U2:U25 the text values 00-23 then in V2 and copy down.
=SUMPRODUCT(--(LEFT(\$L\$2:\$L\$786,2)=U2))

This will give you a count per hour, for your arrival time in column L.

8. ## Re: Calculating number of patients by arrival time (hr)

I am just brain dead today! sorry. That just lost me...sort of. I just tried it and I guess I'm not understanding.

I am trying to calculate how many patients arrived between 1am - 2am, 2am-3am etc...

9. ## Re: Calculating number of patients by arrival time (hr)

Hi and welcome to the forum

All your data is actually text, not values. To convert the dates to real dates, highlight column J, select data/text-to-columns, select next, next, check the DATE button, select FINISH. Repeat for O
This will convert your text dates to dates

Also note, all those merged cells you have there are going to cause problems for you down the road....I just noticed all the cells on O are merged, you will have to un-merge them before you can do the T2C
you can use =TIME(LEFT(L2,2),RIGHT(L2,2),0) to convert the time, in another column. Your times look a bit strange though, you have visits from 1AM up to 23:33PM?

10. ## Re: Calculating number of patients by arrival time (hr)

Thanks everyone! I'm very close I can taste it. With the help of an earlier post...the times have been fixed and merged cells fixed. Now, I just need to figure out how to count the number of arrivals between 1am - 2am, 2am - 3pm etc... and by date.

See attached updated file.

11. ## Re: Calculating number of patients by arrival time (hr)

I thought the question was, you wanted to count the values for the arrival time. Where did converting dates and times come from!

See the attached for counting the times in column L (Arrival Time).

12. ## Re: Calculating number of patients by arrival time (hr)

yes, yes, yes...that is exactly what I needed. I didn't originally ask for times/dates to be converted...you are correct!! Thank YOU!!!!

13. ## Re: Calculating number of patients by arrival time (hr)

Kevin: I loved the logic you used to count the time frame. Thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1