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 |
#11
|
|||
|
|||
Days to next anniversary
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") Which, in comparing it to Sandy's, is the same except that mine eliminates the call to the MAX function that Sandy used. Rick |
#12
|
|||
|
|||
Days to next anniversary
Ah, but *I* will! argh!
BG -- Biff Microsoft Excel MVP "Sandy Mann" wrote in message ... 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 |
#13
|
|||
|
|||
Days to next anniversary
Bonsour® Rick Rothstein (MVP - VB) avec ferveur ;o))) vous nous disiez :
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") May be this formula is shortest ??? : =DATE( YEAR(TODAY()) + (DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY()) , MONTH(A1) , DAY(A1)) - TODAY() HTH -- @+ ;o))) |
#14
|
|||
|
|||
Days to next anniversary
Character count? Where's your error checking? g
I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count title g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" 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? 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 |
#15
|
|||
|
|||
Days to next anniversary
Let's trim some more characters:
No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count? Where's your error checking? g I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count title g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" 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? 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 |
#16
|
|||
|
|||
Days to next anniversary
....or even a few more...
=IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start date of 02/29/2000...but, so far so good.) *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Let's trim some more characters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count? Where's your error checking? g I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count title g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" 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? 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 |
#17
|
|||
|
|||
Days to next anniversary
...or....assuming text and future values wouldn't be entered as a start date
=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: ...or even a few more... =IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start date of 02/29/2000...but, so far so good.) *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Let's trim some more characters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count? Where's your error checking? g I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count title g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" 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? 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 |
#18
|
|||
|
|||
Days to next anniversary
We can trim a couple mo
=IF(A1,LOOKUP(365,((TEXT(A1,"m/d/")&YEAR(NOW())+{0,1})-TODAY())),"") Startin' to look pretty good! Upon further testing of my previous offering I find it fails if the hire date is a leap day so I'll put that in the round file and "stash" this beauty cooked up by Ron. -- Biff Microsoft Excel MVP "Ron Coderre" wrote in message ... ..or....assuming text and future values wouldn't be entered as a start date =IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: ...or even a few more... =IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start date of 02/29/2000...but, so far so good.) *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Let's trim some more characters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count? Where's your error checking? g I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count title g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" 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? 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 |
#19
|
|||
|
|||
Days to next anniversary
Argh!
Disregard that formula. It fails if the hire date is a leap day. See Ron's beauty! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Let's trim some more characters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count? Where's your error checking? g I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count title g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" 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? 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 |
#20
|
|||
|
|||
Days to next anniversary
Hi Ron
Very nice solution. Just need to remember that the whole world doesn't use US date formats!vbg. It gives some strange results in the UK unless you modify to =IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition to Biff'sNOW() in place of TODAY() you can trim another 2 characters by using"m/d/" or "d/m/" in place of the double dd and double mm--RegardsRoger Govier"Ron Coderre" wrote in ... ..or....assuming text and future values wouldn't be entered as a startdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: ...or even a few more...=IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start date of02/29/2000...but, so far so good.) *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Let's trim some more characters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count? Where's your error checking? g I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count titleg: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+ (DATEVALUE(MONTH(A1)&"/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" 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 howmany days is the anniversary away. Any suggestions? Here is my offering (assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(T ODAY()),MONTH(A1),DAY(A1))TODAY()),MONTH(A1),DAY( A1)),"d") While character count is not an absolute metric, I note thatthis 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 Sandyhas pointed out, so we will see how things shake out on thecharacter count later. Rick |
Thread Tools | |
Display Modes | |
|
|