# Thread: Help with dates (excel 2000)

1. ## Help with dates (excel 2000)

I am in the Air Force and work in mobility and we have to track everyone's training dates and shot dates. I can transfer all the data I want from Acess into Excel but I get these long lists of everyone's dates then I have to go in by hand and find all the overdues and figure the percentage. I was wondering if there is a way to let excel find all the overdues from the current date and then figure a percentage. Any help would be great thanks.

2. ## Re: Help with dates (excel 2000)

If the dates are in column B, and there is no other numerical data in Column B, try:

=COUNTIF(B:B,"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/COUNT(B:[img]/forums/images/smilies/cool.gif[/img]

The left side from the "/" gives the count of dates more than a year old.

There might be a shorter array formula.

3. ## Re: Help with dates (excel 2000)

I think that your formula will return True for any date more more one or more DAYS less than today. Did you by chance mean:

<pre>=B1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
</pre>

4. ## Re: Help with dates (excel 2000)

Oops! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

5. ## Re: Help with dates (excel 2000)

<P ID="edit" class=small>(Edited by macropod on 18-Sep-02 13:07. Formula correction: 'YEAR(TODAY())-1' instead of YEAR(TODAY()).)</P>Following on from John's post, if you want to quickly find the dates in Column B that over a year old, you can use the formula:
=B1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
If you put this in row 1 of any available and copy it down, it'll return 'TRUE' for dates over a year old, and 'FALSE' for dates under a year old. Similarly, if you put the same formula into cell B1 as a conditional format with, say, a yellow background, and copy that format down the whole column, all of the cells with dates over a year old will have the yellow background.

Cheers

6. ## Re: Help with dates (excel 2000)

Hi,
If you already have all the data in Access, is there a reason you can't just create a query there to give you the answer you want?

7. ## Re: Help with dates (excel 2000)

I suppose I could do it that way but I guess I'm to scared to mess something up in the entire database. Plus I figured the Excel would be able to do the math for me. I'm kind of clueless on Access, would I be able to find the overdues and percent of overdues in Access

8. ## Re: Help with dates (excel 2000)

Thanks I will try that first thing in the morning.

9. ## Re: Help with dates (excel 2000)

What I would really like is a number at the end of the colum that would say there are x many overdues and that would equal x%

10. ## Re: Help with dates (excel 2000)

By john's method (change the column as appropriate)
The number of overdue is:
=COUNTIF(B:B,"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))

and the percentage is:
=COUNTIF(B:B,"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/COUNT(B:[img]/forums/images/smilies/cool.gif[/img]

or you could combine in one output with descriptionall one line in one cell, change as needed):

="The number of Overdue is "&text(COUNTIF(B:B,"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),"0") &" ("&text(COUNTIF(B:B,"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))/COUNT(B:[img]/forums/images/smilies/cool.gif[/img],"0.0%")&" of "& text(COUNT(B:[img]/forums/images/smilies/cool.gif[/img],"0")&")"

Steve

11. ## Re: Help with dates (excel 2000)

Yes you can do the calculations in Access - I'm attaching a sample db with a report showing what I mean. You can alter the report/query to show exactly what information you need.
Hope that helps.

12. ## Re: Help with dates (excel 2000)

Hi Steve,

Jame wants "a number at the end of the colum that would say there are x many overdues and that would equal x%". That means the'B:B' parameter in your formulae can't be used because it will return a circular reference if used in the same column. To avoid that, you'd have to replace the 'B:B' references with something like:
B1:OFFSET(INDIRECT("B"&ROW()),-1,)

Cheers

13. ## Re: Help with dates (excel 2000)

Good point.
I assumed that since the "text" expression was so large that you could put it in A or C and have it "overlap" into B though I probably should have explicitly stated that.
Steve

14. ## Re: Help with dates (excel 2000)

Instead of the high-falutin "B:B" reference style I suggested, you can use the actual range of the data, such as "B4:B3000".

But what I -really- want to know is, what is a "shot" date? <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

15. ## Re: Help with dates (excel 2000)

I assumed it was some inoculation date "when they got their shot" and they needed annual booster shots,hence the need to look at %overdue.

Even though it was the military, I assumed if was not when they got shot, since I wouldn't imagine you would need (or want) any kind of booster when you were overdue! (though it is the military so I might be wrong - "it's been a year since I was shot and I need another one to stay out of combat" <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> )

Steve

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•