# Thread: Calulation based on Date (2002)

1. ## 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. ## 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. ## 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. ## Re: Calulation based on Date (2002)

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

5. ## 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. ## Re: Calulation based on Date (2002)

Something like

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

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

14. ## 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. ## 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 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
•