Continual date countdown/reminder (Help!)

"GB" wrote in message
...
Your date is in A1.

Then use


=Int(IF(DATE(YEAR(NOW()),MONTH(A1),DAY(A1))=NOW() ,DATE(YEAR(NOW()),MONTH(A1
),DAY(A1))-NOW(),DATE(YEAR(NOW()+1),MONTH(A1),DAY(A1))-NOW())+1)


I'm soooo sorry, I had a bracket in the wrong place. Try this:-

=Int(IF(DATE(YEAR(NOW()),MONTH(A1),DAY(A1))=NOW() ,DATE(YEAR(NOW()),MONTH(A1
),DAY(A1))-NOW(),DATE(YEAR(NOW())+1,MONTH(A1),DAY(A1))-NOW())+1)

(I am not very proud of this formula, and I hope someone else will come up
with a nice neat solution using Mod 365 or something like that.)

Geoff







"Mark A. Fitch" wrote in message
nk.net...
I want to learn how to create formulas that will continually count down
the
remaining days to a set date year after year. Example: someone's
birthday
or a select holiday.

See above, apologies for mis-placed bracket.

"Mark A. Fitch" wrote in message
ink.net...
GB,
I used your formula and I get negative numbers for days that have already
passed. Any suggestions?

Gord Dibben,
I get a number in days from the date of birth. I do not know how to
exclude
the year in the DOB cell from the calculation. Any suggestions.


"Gord Dibben" wrote in message
...
Mark

With your birthday in A1 enter this in B1

=A1-TODAY() & " Days left until Mark's birthday"

Gord Dibben Excel MVP XL2002


On Sun, 14 Sep 2003 20:14:29 GMT, "Mark A. Fitch"
wrote:

I want to learn how to create formulas that will continually count down
the
remaining days to a set date year after year. Example: someone's
birthday
or a select holiday.

"Daniel.M" wrote in message
...
Hi,

With your date in A1:

=MIN(MOD((DATE(YEAR(TODAY())+{0;1},MONTH(A1),DAY(A 1))-TODAY()),733))

Regards,

Daniel M.

Hi, Daniel

Can you just help me here with your formula, please? Stripping out the Mod
function, you have:
=MIN(DATE(YEAR(TODAY())+{0;1},MONTH(A1),DAY(A1))-TODAY())

I was trying to do the same with my cumbersome If statement. Can you explain
a bit how the bit in curly brackets works?

Following the same logic, I entered
=+{2;3}
I was hoping for the result of 5, but actually got 2

On the other hand, =MAX({2;3}) does give the expected value of 3

So curly brackets work with some functions but not others. How do I find out
which? :-)

Regards

Geoff

"Daniel.M" wrote in message
...
Hi,

With your date in A1:

=MIN(MOD((DATE(YEAR(TODAY())+{0;1},MONTH(A1),DAY(A 1))-TODAY()),733))

Regards,

Daniel M.

Sorry, Daniel - two other questions:

Why does this formula NOT have to be entered as an array formula -
ctrl-shift-enter?

Is there any significance in using semi-colons rather than commas?

Thanks

Geoff

Hi,


=MIN(MOD((DATE(YEAR(TODAY())+{0;1},MONTH(A1),DAY(A 1))-TODAY()),733))

Hi, Daniel

Can you just help me here with your formula, please? Stripping out the Mod
function, you have:
=MIN(DATE(YEAR(TODAY())+{0;1},MONTH(A1),DAY(A1))-TODAY())

I was trying to do the same with my cumbersome If statement. Can you
explain
a bit how the bit in curly brackets works?


In A1 you have a birthdate. Let's say I was born on Nov 6th.
The {0;1} produces an array of two dates : Nov 6th 2003, Nov 6th 2004.

To know how much days remaining before next birthday, you have to do:
Nov 6th - Today.
In that case, it's working (giving two positive numbers).
You take the MINimum (MODulo won't change those numbers) and you're in
business!

But what if I was born on May 6th.
Now, the substraction gives one negative number (my birthday occured EARLIER
in the year) and one positive number.

I have to keep the positive one. Doing MOD(n,733) is one way of making sure
the negative numbers will transform into big enough positive numbers so that
the MIN function will keep the original positive number.


Regards,

Daniel M.

Hi,

Sorry, Daniel - two other questions:

Why does this formula NOT have to be entered as an array formula -
ctrl-shift-enter?

You're right into thinking it's certainly an 'implicit' array formula.
However, the MIN function takes an array as argument and returns a scalar.

Maybe others can comment if there is a very CONSTANT and logical RULE Excel
uses to govern these matters.

However, it doesn't hurt to enter it as an ARRAY formula if you like it. ;-)



Is there any significance in using semi-colons rather than commas?

Semi-colons (between '{' and '}') is a standard way to represent VERTICAL
array constant in all ISO-latin Excel platforms.

Commas is ONE way to represent HORIZONTAL array constant that is depending
on your Windows regional settings. Lots of people have the commas as decimal
separator and use '\' or '.' as their horizontal array constant separator,
especially in iso-latin foreign languages (french, spanish).

When I have the choice of the dimension (horizontal or vertical) in which I
want to build my array, I'll choose the standard one.

Regards,

Daniel M.

Thanks, Daniel. I think I have got most of that. I'll fiddle around with
Excel, as otherwise I'll have forgotten it by the end of the week.

Geoff

"Daniel.M" wrote in message
...
Hi,

Sorry, Daniel - two other questions:

Why does this formula NOT have to be entered as an array formula -
ctrl-shift-enter?

You're right into thinking it's certainly an 'implicit' array formula.
However, the MIN function takes an array as argument and returns a scalar.

Maybe others can comment if there is a very CONSTANT and logical RULE
Excel
uses to govern these matters.

However, it doesn't hurt to enter it as an ARRAY formula if you like it.
;-)



Is there any significance in using semi-colons rather than commas?

Semi-colons (between '{' and '}') is a standard way to represent VERTICAL
array constant in all ISO-latin Excel platforms.

Commas is ONE way to represent HORIZONTAL array constant that is depending
on your Windows regional settings. Lots of people have the commas as
decimal
separator and use '\' or '.' as their horizontal array constant separator,
especially in iso-latin foreign languages (french, spanish).

When I have the choice of the dimension (horizontal or vertical) in which
I
want to build my array, I'll choose the standard one.

Regards,

Daniel M.