# Appointments due in excel ?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### EvileYe

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

#### EvileYe

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.

#### XL Guru

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

#### EvileYe

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

#### CastleHeart

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

#### CastleHeart

=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

#### EvileYe

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.

#### CastleHeart

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.

#### EvileYe

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.

#### EvileYe

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.

#### CastleHeart

Well ALRIGHTY then!

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

lookin' at a THING in a bag!

#### XL Guru

>> 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

#### EvileYe

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.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On