LinkBack URL
About LinkBacks
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!
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!
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
Please explain the data (what is represents) that is available in the attached spreadsheet.
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.
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
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!
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!!!)
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...
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
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.
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).
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!!!!
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
Register To Reply
Bookmarks