Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Whiteman AFB, Missouri, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Help with dates (excel 2000)

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

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    Paul Edstein
    [MS MVP - Word]

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Whiteman AFB, Missouri, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    New Lounger
    Join Date
    Sep 2002
    Location
    Whiteman AFB, Missouri, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with dates (excel 2000)

    Thanks I will try that first thing in the morning.

  9. #9
    New Lounger
    Join Date
    Sep 2002
    Location
    Whiteman AFB, Missouri, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    Paul Edstein
    [MS MVP - Word]

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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>
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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 LastLast

Posting Permissions

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