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
|
|||
|
|||
Days to next anniversary
I want to show the number of days between the current date (today) and the
hiring date in order to provide an indication of how many days is the anniversary away. Any suggestions? Thanks, Stefano |
#2
|
|||
|
|||
Days to next anniversary
This worked for me: with the anniversary date in cell A1,
=IF(MONTH(TODAY())=MONTH(A1),DATE(YEAR(TODAY()),M ONTH(A1),DAY(A1))- TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY()) The formula adjusts the anniversary date to the same day in the current year if that month has not occurred yet, and to next year if the anniversary month occurred already. |
#3
|
|||
|
|||
Days to next anniversary
"Dave O" wrote in message
oups.com... This worked for me: with the anniversary date in cell A1, =IF(MONTH(TODAY())=MONTH(A1),DATE(YEAR(TODAY()),M ONTH(A1),DAY(A1))- TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY()) Returns -7 to -1 for dates July 1 2007 to July 7 2007 respectively. My suggestion would be: =DATEDIF(TODAY(),MAX(A1,DATE(YEAR(TODAY())+(DATE(Y EAR(TODAY()),MONTH(A1),DAY(A1))TODAY()),MONTH(A1) ,DAY(A1))),"d") -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#4
|
|||
|
|||
Days to next anniversary
Try this:
A2 = hire date =IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY())) -- Biff Microsoft Excel MVP "smaruzzi" wrote in message ... I want to show the number of days between the current date (today) and the hiring date in order to provide an indication of how many days is the anniversary away. Any suggestions? Thanks, Stefano |
#5
|
|||
|
|||
Days to next anniversary
"T. Valko" wrote in message
... A2 = hire date =IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY())) Returns 358 for date in A2 of June 30 2007 and 7 to 1 for July 1 2007 to July 7 2007 respectively. Why return an empty string for dates in the future? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... Try this: A2 = hire date =IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY())) -- Biff Microsoft Excel MVP "smaruzzi" wrote in message ... I want to show the number of days between the current date (today) and the hiring date in order to provide an indication of how many days is the anniversary away. Any suggestions? Thanks, Stefano |
#6
|
|||
|
|||
Days to next anniversary
Thanks to each one of you. I think Sandy's suggestion is the correct one.
Stefano "Sandy Mann" wrote: "Dave O" wrote in message oups.com... This worked for me: with the anniversary date in cell A1, =IF(MONTH(TODAY())=MONTH(A1),DATE(YEAR(TODAY()),M ONTH(A1),DAY(A1))- TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY()) Returns -7 to -1 for dates July 1 2007 to July 7 2007 respectively. My suggestion would be: =DATEDIF(TODAY(),MAX(A1,DATE(YEAR(TODAY())+(DATE(Y EAR(TODAY()),MONTH(A1),DAY(A1))TODAY()),MONTH(A1) ,DAY(A1))),"d") -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#7
|
|||
|
|||
Days to next anniversary
"Sandy Mann" wrote in message
... "T. Valko" wrote in message ... A2 = hire date =IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY())) Returns 358 for date in A2 of June 30 2007 and 7 to 1 for July 1 2007 to July 7 2007 respectively. Ooops! Back to the drawing board! Why return an empty string for dates in the future? I'm thinking that the hire date can't be today. You don't have an anniversary date until you actually have a start date. -- Biff Microsoft Excel MVP |
#8
|
|||
|
|||
Days to next anniversary
It's alright Biff, it's Sunday - no one will notice g
-- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... "Sandy Mann" wrote in message ... "T. Valko" wrote in message ... A2 = hire date =IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY())) Returns 358 for date in A2 of June 30 2007 and 7 to 1 for July 1 2007 to July 7 2007 respectively. Ooops! Back to the drawing board! Why return an empty string for dates in the future? I'm thinking that the hire date can't be today. You don't have an anniversary date until you actually have a start date. -- Biff Microsoft Excel MVP |
#9
|
|||
|
|||
Days to next anniversary
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many days is the anniversary away. Any suggestions? Here is my offering (assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))TODAY()),MONTH(A1),DAY(A1 )),"d") While character count is not an absolute metric, I note that this formula is the shortest one offered so far except for Biff's, mine is one character longer than Biff's formula; but, as Sandy pointed out, Biff will have to modify his formula to account for the problem Sandy has pointed out, so we will see how things shake out on the character count later. Rick |
#10
|
|||
|
|||
Days to next anniversary
While character count is not an absolute metric...
That was supposed to say... "While character count is not an absolute metric to necessarily strive for..." Rick |
Thread Tools | |
Display Modes | |
|
|