+ Reply to Thread
Results 1 to 4 of 4

Counting when a Time is within 30 minutes of a time in a different Column

  1. #1
    Registered User
    Join Date
    11-12-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    14

    Question Counting when a Time is within 30 minutes of a time in a different Column

    As described in this thread http://www.excelforum.com/excel-form...in-column.html I am conducting an audit in my clinic.

    Last time I had a question the Forum helped me solve the problem in record time. I'm back with another question so that I can start crunching the resulting numbers.

    I have attached a single sheet from my workbook and am looking for help populating columns W, X and Z.

    Column W:
    I would like this column to show "Yes" if Column G equals or is less than Column B + 30min. I.e. if the Dr sees the patient anytime before 30minutes after the appointment. I thought the formula would be =IF(G2=<(B2+00:30:00),"Yes","No") but this doesn't seem to work.

    Column X:
    I would like this column to show "Yes" if Column G is more than Column B + 30min. Again, I thought the formula would be =IF(G2>(B2+00:30:00),"Yes","No") but this doesn't work either.

    Column Z:
    I would like this column to show "Yes" if Column C is equal or less than Column B as long as Column A is less than Column B. I.e "Yes" if the nurse sees the patients on or before their appointment time as long as they booked in before their appointment (therefore not displaying late attenders). I can think of a formula in my head for this, but I don't know how I would put it into excel language).

    Thanks in advance for any help you can offer.
    Attached Files Attached Files
    Last edited by danieloverton1984; 11-27-2013 at 03:54 PM. Reason: Solved

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Counting when a Time is within 30 minutes of a time in a different Column

    Hi

    Try in w2 and copy down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Same way for the others.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    11-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Counting when a Time is within 30 minutes of a time in a different Column

    Here is A different way to do it:

    Column W: =IF((G2-B2)>TIME(0,30,0),"No","Yes")

    Column X: =IF((G2-B2)>TIME(0,30,0),"Yes","No")

    Column Z: =IF(AND(A2<B2,C2<B2),"Yes","")


    Hope this helps

  4. #4
    Registered User
    Join Date
    11-12-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Counting when a Time is within 30 minutes of a time in a different Column

    Quote Originally Posted by Cavinaar View Post
    Here is A different way to do it:

    Column W: =IF((G2-B2)>=TIME(0,30,0),"No","Yes")

    Column X: =IF((G2-B2)>TIME(0,30,0),"Yes","No")

    Column Z: =IF(AND(A2<B2,C2<=B2),"Yes","No")


    Hope this helps
    Thanks, this has worked perfect. I just tweaked column W and Z to show results where the Dr time was equal to 30mins late or less and where the nurse saw the patient on as well as prior to their appointment respectively. Thanks for giving me the foundation and allowing me to tweak it that little bit. I really do wish I understood how to use excel.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to get a total 'time in minutes' from a column that has military times entered
    By bustech in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2013, 07:11 AM
  2. Replies: 21
    Last Post: 11-30-2012, 07:28 PM
  3. Replies: 5
    Last Post: 07-11-2012, 12:27 PM
  4. [SOLVED] Subtract date/time from data/time and get difference in minutes
    By zit1343 in forum Excel General
    Replies: 2
    Last Post: 06-18-2012, 11:23 AM
  5. Converting hours and minutes in military time to minutes
    By Argile79 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-13-2010, 02:42 PM

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