Expiring

[Travis King]

Please look at my example first of all here, then read my question:
http://img378.imageshack.us/img378/5691/autochange4tt.jpg
If we entered an item like '1', and after a year, it expires and goes back
to '0', is there a way to automatically do this in Excel, so I don't have to
go through and change it to 0 manually after a year for each day? Thanks.

 

[Sandy Mann]

Try:

=IF(TODAY()<DATE(YEAR(D7)+1,MONTH(D7),DAY(D7)),1,0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[kkchoh]

Try

=IF(Year(Today())=Year(D7),1,0)

Or

=--(Year(Today())=Year(D7))

 

[Travis King]

Thanks, I'll give it a shot.

Try

=IF(Year(Today())=Year(D7),1,0)

Or

=--(Year(Today())=Year(D7))

 

[Sandy Mann]

Thanks, I'll give it a shot.

"kkchoh" <kkchohATyahoo.com> wrote in message

Note that with 31/1/05 (British styled date) in D7 you will always get 0
returned

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Travis King]

Now here's the problem that might make this more difficult, I was using the
1 and the 0 as a sample. My dad wants to keep track of the amount of days
he has used FMLA. After a year, it expires or is removed and is no longer
counted. That's why I needed to know. The problem is, when I use 'FMLA',
it doesn't seem to want to accept it. I've adjusted the formula by plugging
in 'FMLA' instead of 1. It will not accept the formulas now. It was
working the way that Sandy told me to try when I was using numbers. The 0
stood for the days he didn't use FMLA and the 1 stood for the days that he
did use FMLA. I wanted to replace the 1 with the word, 'FMLA' instead so it
was easy to tell what it meant. Then, he also wanted to know how many
FMLA's he used. I used the 'countif' formula for that, and this worked, but
using this formula doesn't when it comes to expiring. I will still use 0
for days he hasn't used FMLA, but I want to use the word 'FMLA' for days he
has. How would the formula work now? Sandy's method was the way that
worked correctly. The other methods kind of worked, but didn't work the way
I wanted them to. Thanks again to the both of you.

 

[Travis King]

Here's a link to what it looks like now, and again, I want it set up the
same way as the last 1 only now we're dealing with FMLA instead of 1.
Remember, I had issues when plugging in FMLA into the formula instead of 1.
It didn't matter if I typed FMLA, 'FMLA, or "FMLA". When I typed in "FMLA",
it actually went through, but I got a name error and if I did slight
adjustments to the formula, I got a value error instead.
http://img404.imageshack.us/img404/5126/expire27rv.jpg

 

[Paul B]

Travis, to change Sandy's formula from 1 to FMLA
=IF(TODAY()<DATE(YEAR(E7)+1,MONTH(E7),DAY(E7)),"FMLA",0)

but I don't see what you are doing, if you want to count the number of days
he uses in a year why not just put the dates in column A and FMLA in column
B and then use something like this

=SUMPRODUCT((YEAR(A1:A100)=2005)*(B1:B100="FMLA"))

or even easier just put ONLY the dates that he uses FMLA in column A and
then just count the dates for the year

=SUMPRODUCT((A1:A100>0)*(YEAR(A1:A100)=2005))
or something like this
="FMLA Used " & SUMPRODUCT((A1:A100>0)*(YEAR(A1:A100)=2005))&" Time(s) In
2005"
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Sandy Mann]

I don' really follow what it is that you are saying but you are correct in
that to have to enclose text in double quotes, (in a formula note that you
do not need quotes when entering a word into a cell directly):

=IF(TODAY()<DATE(YEAR(H7)+1,MONTH(H7),DAY(H7)),"FMLA",0)

When you say that you got a NAME? error I assume that you are using FMLA
without the double quotes in the COUNTIF() function. If so then use:

=COUNTIF(A6:AD6,"FMLA")

If it helps then you can send me a sample sheet. Just change my addres as
it says in the signature.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk