Continual date countdown/reminder (Help!)

  • Thread starter Thread starter Mark A. Fitch
  • Start date Start date
M

Mark A. Fitch

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.
 
Thank you. I will give it a go.
GB said:
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 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
Click to expand...
 
Marl

Thought it was too simple. Did not read "all" the info.

You did state "year after year". I just did it for this year with the
birthday this year in A1, not YOB.

Will try to get back, but am sure one of the Formula gurus will solve for you.

Gord
 
Hi,

With your date in A1:

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

Regards,

Daniel M.
 
Thank you, Daniel. It seems to work great. Before I email the completed SS
back to my sister I think I will add a daily count down based on each
persons life expectancy. To morbid do you think?
 
You're welcome.
I think I will add a daily count down based on each
persons life expectancy. Too morbid do you think?
Click to expand...


Depends if they are close to you.
And if not, how rich! ;-)

Regards,

Daniel M.
 
GB said:
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)
Click to expand...

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)
 
See above, apologies for mis-placed bracket.

Mark A. Fitch said:
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.
Click to expand...
 
Daniel.M said:
Hi,

With your date in A1:

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

Regards,

Daniel M.
Click to expand...

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 said:
Hi,

With your date in A1:

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

Regards,

Daniel M.
Click to expand...
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,
Sorry, Daniel - two other questions:

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

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?
Click to expand...

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
 
Back
Top