If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Continual date countdown/reminder (Help!)
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. |
#3
|
|||
|
|||
Continual date countdown/reminder (Help!)
"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 |
#4
|
|||
|
|||
Continual date countdown/reminder (Help!)
"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 |
#5
|
|||
|
|||
Continual date countdown/reminder (Help!)
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. |
#6
|
|||
|
|||
Continual date countdown/reminder (Help!)
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. |
#7
|
|||
|
|||
Continual date countdown/reminder (Help!)
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. |
Thread Tools | |
Display Modes | |
|
|