1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Appointments due in excel ?

Discussion in 'Business Applications' started by EvileYe, Sep 17, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. EvileYe

    EvileYe Thread Starter

    Joined:
    Aug 30, 2003
    Messages:
    1,281
    Hi,
    I am a newby to Excel so please let me know if I am on the wrong track here.

    I would like to create a spreadsheet that will
    display "Appointments due in 30 days"

    Basically our employee's require a medical every 3 years, and I would like an easy way to keep track of this.

    I would like to list Employee's by Name and the date they had a medical, then when I open the spreadsheet it would automatically remind me if an employee has a medical due in say 30 days time and display the names of all employee's due in a drop down list.

    Is this possible in Excel ?
    Could someone point me in the right direction ?

    Thanks,

    EvileYe
     
  2. EvileYe

    EvileYe Thread Starter

    Joined:
    Aug 30, 2003
    Messages:
    1,281
    Ok, I have worked out how to get my data for the values I have set.
    Is it possible to display on a different worksheet only the Names of employees and days left to go when it is less than 30 days ?

    IE: Worksheet 2 contains all the employees and dates etc, I would like Worksheet 1 to display those employees who have a medical approaching in 30 days or less. The Format I would like displayed is employee name and days left to go or overdue.

    Any help appreciated !

    Thanks,
    EvileYe.
     
  3. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Whatever you have so far, you might prefer the DATEDIF function (NB - you won't find it in XL Help unless you have 2K [although I can't vouch for 2K3]).

    As the simplest example, let's say you have "Name" in A1 with names below, "DateLastMed" in B1 with respective dates below, and "Due?" in C1.

    Enter this formula in C2 -

    =IF(DATEDIF(B2,TODAY(),"m")<35,"",IF(DATEDIF(B2,TODAY(),"m")<36,365-DATEDIF(B2,TODAY(),"yd"),DATEDIF(B2,TODAY(),"yd")*-1))

    and drag it down (this is a continuous string, make sure you have no spaces). Select A1:C1, and Data -- Filter -- AutoFilter. Now you can use the dropdown in C1 at any time to show non-blanks.

    I tested the formula a little. The only health warning is that DATEDIF can clash with leap years, but give it a go & post back with how you get on.

    Rgds,
    Andy
     
  4. EvileYe

    EvileYe Thread Starter

    Joined:
    Aug 30, 2003
    Messages:
    1,281
    Thanks for your help Andy !
    I have achieved a similar result using the Formula below.

    Basically what I am using now is this:

    A1 =NOW()
    A2 Names B2 Date of Last Med C2 Date Med Due D2 Days Till Med Due

    In D3 is this formula which I copied down the column, =DAYS360(C3,$A$1)I am also using Conditional Formatting so the values that are greater than or Equal to -30 are displayed in Bold Red Type.

    I tried using your idea of Auto Filter to display only those that are due in 30 days or less, whick works quite good.
    I would still like to know is there a way I could display The due in 30 days or less on a seperate sheet or using something similar to the Auto Filter but have a drop down box that only displays the due in 30 days or less list whilst still leaving the rest of the sheet visible ?.

    I hope that makes sense :)

    Thanks,
    EvileYe
     
  5. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    This may be too roundabout....

    WORKSHEET2 contains the employee list
    WORKSHEET1 column A has the formulas:

    =IF(Sheet2!D1<=30,Sheet2!A1,"") and so on
    Column B fas the formulas: =IF(A1="","",Sheet2!D1)


    The worksheet you open with has a cell: =IF(MIN(Sheet3!D2: D9)<=30,"MEDICAL DUE","") which will alert you that there is someone(s) in the list that is less than 30.

    Next to that cell, create a button who's macro takes you to sheet1 and sorts the sheet according to column B. You will see only the names of the employees with medicals coming due in a list according to priority.

    Include a "RETURN" button macro on that sheet which will return you to the desired place in your opening sheet.

    - Castleheart
     
  6. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Let me add.... the formula

    =IF(Sheet2!D1<=30,Sheet2!A1,"")

    was written with assumption that your column D was the "Days till medical" figure as you said in your response.Column A was the name

    also I used a =C2-$A$1 which gave me a positive number of days remaining rather than your =DAYS360(C2,$A$1) so you probably will have to switch the < > around for your formula.

    -C
     
  7. EvileYe

    EvileYe Thread Starter

    Joined:
    Aug 30, 2003
    Messages:
    1,281
    Thanks CastleHeart,
    I need to have the sheet easy to use for other users and have no idea about writing macros so I have decided to go along with using the auto filter to display values less than 30 on the same sheet. What I have now in "D" is this formula

    =DATEDIF(TODAY(),C14,"d")
    Which works fine except for one small hitch,
    The problem I have now is if the date in "C14" is before todays date it returns #NUM! Can I add something to this formula to get it to say "OVERDUE" or return a negative number when the value is less than 0 days remaining till Medical ?

    EvileYe.
     
  8. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    try this :

    =IF((TODAY()-C14>0),"OVERDUE "&TODAY()-C14&" DAYS",C14-TODAY())

    - C

    You might want to simplify the expression by having a cell that is the date cell TODAY() - and then just referring to that cell in the formula. Other formulas using the date could then also reference that cell.
     
  9. EvileYe

    EvileYe Thread Starter

    Joined:
    Aug 30, 2003
    Messages:
    1,281
    Ok Thanks for the help ! I will try it as soon as I get a chance and I'll let you know how I go. :)

    EvileYe.
     
  10. EvileYe

    EvileYe Thread Starter

    Joined:
    Aug 30, 2003
    Messages:
    1,281
    Ok I tried it and It Works great !!! Thanks CastleHeart, I can tweak it all around a bit now and start to play with the macros and stuff you told me about.

    EvileYe.
     
  11. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Well ALRIGHTY then! (y)

    With a little mix of that formula and some of XLGs wizardry and a touch of "MACRO" mixed with a dash of your creativity and you have it just like you want.






    - C
    :cool:




    lookin' at a THING in a bag!
     
  12. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> is there a way I could display The due in 30 days
    >> or less on a seperate sheet

    Yes, & without code, with a pivot table.

    Each record would need (a) Castleheart's formula tweaked some (b) a TRUE/FALSE field (True = due or overdue, False = not due yet) to be the page field in the pivot table ie: show only those records due or overdue (c) an =TODAY() field.

    The pivot table would (a) use a dynamic named range for the data source, to autoadjust for new/deleted records (b) use a calculated field (=(Date-MedDue)*-1, so "Al" would show as -231, ie : should have had a med 231 days ago, whereas "Mike" = 11, ie: is due in eleven days) (c) be set to refresh on file open, for the benefit of your "other users".

    If interested, I could send a small demo privately & do some hand-holding.

    Rgds,
    Andy
     
  13. EvileYe

    EvileYe Thread Starter

    Joined:
    Aug 30, 2003
    Messages:
    1,281
    Ok XL Guru, I am definatley going to need some hand holding here.
    I just looked at creating a Pivot Table and it is all chinese to me.

    There are people who will use this in my office who have even less knowledge of excel than me. So the less user input I need the better. :) If I can have it open and display Meds due/overdue automatically that would be perfect.


    So send away !

    Thanks,
    EvileYe.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/165392

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice