Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calulation based on Date (2002)

    I am in the process of developing a training matrix. What I would like to do is create a formula would return a 'Current' or 'Not Current' value. This would be based on the date training was conducted and the period that the training is valid (which would be built into the formula) - this could be a number of months or years depending on the training carried out. Once the valid period expired the formula would return a 'Not Current' value. Any of the Guru's got any ideas?

    Thanks

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calulation based on Date (2002)

    The Analysis ToolPak add-in contains the EDATE function. Say that the training date is in cell A1, and the number of months that the training is valid in cell B1, then the following formula should do what you want:

    =IF(EDATE(A1,B1)<TODAY(),"Not Current","Current")

    If B1 contains a number of years, multiply its value by 12.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulation based on Date (2002)

    Hans,

    Thanks very much for the quick response. Could I substitute B1 in your formula to be the valid period, ie 6,18 months etc? as I am trying to keep the number of columns to a minumum.

    Thanks

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calulation based on Date (2002)

    Of course, you can use literal values and cell references interchangeably in formulas.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulation based on Date (2002)

    Hans, That works great - Thanks.
    What would the formula change to so that blank date cells returned a 0 or empty result in the calculated cell?
    I want to be able to do this so I can fill down the cells as it is they all return a "Not Current" result. Thanks for your help

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calulation based on Date (2002)

    Something like

    =IF(A1="","",IF(EDATE(A1,18)<TODAY(),"Not Current","Current"))

  7. #7
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulation based on Date (2002)

    Spot on Hans - Thanks very much

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulation based on Date (2002)

    Hans - I assume the 18 is the variable for the number of months before the calculation will change from "Current" to 'Not Current"

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calulation based on Date (2002)

    In an earlier reply, I used a reference to cell B1 as the number of months that the training is valid. You then asked if you could use a number like 6 or 18 instead. In my last reply, I used 18 as an example; you can replace it by the appropriate number of months.

  10. #10
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulation based on Date (2002)

    But suggest you consider keeping that column as it makes the sheet easier to edit: if the driving force is related to printing, you can always hide it?

  11. #11
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulation based on Date (2002)

    I was hoping some Excel guru might be able to held with a variation to the formula - I need to add in another component that would return and message "one month left" when the expiry date was within one month ie at 17 months

    I'm sure it is simple - but I'm learning (slowly!)

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calulation based on Date (2002)

    With the current date in B1, the training date in B2, the number of months the training is valid in B3, and the duration of the warning period in B4, the formula becomes

    =IF(B2="","",IF(EDATE(B2,B3-B4)<B1,IF(EDATE(B2,B3)<B1,"Not Current","One Month Left"),"Current"))

    See attached workbook.

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calulation based on Date (2002)

    Not simple, too much stuff to string together. I'll use a different function that may make more sense.
    <pre>=IF(A1="","",IF(DATEDIF(A1,TODAY(),"m")<17,"C urrent",
    IF(DATEDIF(A1,TODAY(),"m")=17,"One Month Left","Not Current")))
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calulation based on Date (2002)

    Sam - that works fine Thanks, however I need the calculation to return an empty value when there is no date entered. This will allow me to be able to fill the formula down. Any thoughts?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calulation based on Date (2002)

    Sam's formula already does that. The first part of the formula<pre>=IF(A1="","",</pre>

    means that if A1 is empty (equal to an empty string), the formula will return a blank (an empty string)

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
  •