+ Reply to Thread
Results 1 to 13 of 13

Calculating number of patients by arrival time (hr)

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    Dayton, OH
    MS-Off Ver
    Excel 2003
    Posts
    11

    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!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-30-2013
    Location
    Dayton, OH
    MS-Off Ver
    Excel 2003
    Posts
    11

    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. #3
    Registered User
    Join Date
    07-01-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    8

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

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

  4. #4
    Registered User
    Join Date
    05-30-2013
    Location
    Dayton, OH
    MS-Off Ver
    Excel 2003
    Posts
    11

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-01-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    8

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

    In your sheet1 spreadsheet,
    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-30-2013
    Location
    Dayton, OH
    MS-Off Ver
    Excel 2003
    Posts
    11

    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. #7
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    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.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  8. #8
    Registered User
    Join Date
    05-30-2013
    Location
    Dayton, OH
    MS-Off Ver
    Excel 2003
    Posts
    11

    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. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,787

    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?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    05-30-2013
    Location
    Dayton, OH
    MS-Off Ver
    Excel 2003
    Posts
    11

    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.
    Attached Files Attached Files

  11. #11
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    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).
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-30-2013
    Location
    Dayton, OH
    MS-Off Ver
    Excel 2003
    Posts
    11

    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. #13
    Registered User
    Join Date
    07-01-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    8

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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