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 |
#21
|
|||
|
|||
Days to next anniversary
Not sure what happened with that posting???. Hope this comes out
clearer. 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 byusing"m/d/" or "d/m/" in place of the double dd and double mm--RegardsRoger Govier"Roger Govier" wrote in l... Hi Ron Very nice solution. Just need to remember that the whole world doesn't use US dateformats!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 toBiff'sNOW() in place of TODAY() you can trim another 2 characters byusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Ron wrote text and future values wouldn't be entered as astartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "RonCoderre" wrote: ...or even a fewmore...=IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start dateof02/29/2000...but, so far so good.) *********** Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trim some morecharacters: 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 upwith a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But thisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I stillretain 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 errorchecking: =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 .. . I want to show thenumber of days between the current date(today) and the hiringdate in order to provide an indication of howmany days is theanniversary away. Any suggestions? Here is my offering(assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR( TODAY()),MONTH(A1),DAY(A1))TODAY()),MONTH(A1),DAY (A1)),"d") While charactercount is not an absolute metric, I note thatthis formula is theshortest 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 thecharactercount later. Rick |
#22
|
|||
|
|||
Days to next anniversary
Still messed up, even though clear on my screen before posting. Last
attempt. 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 placeof the double "dd "and double "mm".--RegardsRoger Govier"Roger Govier" wrote in l... Not sure what happened with that posting???. Hope this comes outclearer. Hi Ron Very nice solution. Just need to remember that the whole world doesn't use US dateformats!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 toBiff'sNOW() in place of TODAY() you can trim another 2 charactersbyusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Roger Govier" wrote in l... HiRon Very nice solution. Just need to remember that the whole worlddoesn't use US dateformats!vbg. It gives some strange results in theUK unless you modifyto=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in additiontoBiff'sNOW() in place of TODAY() you can trim another 2 charactersbyusing"m/d/" or "d/m/" in place of the double dd anddoublemm--RegardsRoger t.com text and future values wouldn't be entered asastartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP"RonCoderre" wrote: ...or even afewmore...=IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a startdateof02/29/2000...but, so far so good.) ***********Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trimsome morecharacters: 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 bl... Charactercount?Where's your error checking? g I was trying to comeupwith a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() Butthisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and Istillretain the character count titleg: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)& "/"&DAY(A1))TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having someerrorchecking: =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 . .. I want toshow thenumber of days between the current date(today) and thehiringdate in order to provide an indication of howmany days istheanniversary away. Any suggestions? Here is myoffering(assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(Y EAR(TODAY()),MONTH(A1),DAY(A1))TODAY()),MONTH(A1) ,DAY(A1)),"d") Whilecharactercount is not an absolute metric, I note thatthis formula is theshortest one offered so far except for Biff's,mine isone character longer than Biff's formula; but, as Sandy pointedout,Biff will have to modify his formula to account for theproblem Sandyhas pointed out, so we will see how things shake outon thecharactercount later. Rick |
#23
|
|||
|
|||
Days to next anniversary
Thanks, Roger
and, very true about the date format....But, I had it in the back of my mind that the formula could easily be tweaked for non-US date formats. Hopefully, that holds true. *********** Best Regards, Ron XL2002, WinXP "Roger Govier" wrote: Not sure what happened with that posting???. Hope this comes out clearer. 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 byusing"m/d/" or "d/m/" in place of the double dd and double mm--RegardsRoger Govier"Roger Govier" wrote in l... Hi Ron Very nice solution. Just need to remember that the whole world doesn't use US dateformats!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 toBiff'sNOW() in place of TODAY() you can trim another 2 characters byusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Ron wrote text and future values wouldn't be entered as astartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "RonCoderre" wrote: ...or even a fewmore...=IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start dateof02/29/2000...but, so far so good.) *********** Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trim some morecharacters: 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 upwith a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))TODAY() But thisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I stillretain 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 errorchecking: =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 .. . I want to show thenumber of days between the current date(today) and the hiringdate in order to provide an indication of howmany days is theanniversary away. Any suggestions? Here is my offering(assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR( TODAY()),MONTH(A1),DAY(A1))TODAY()),MONTH(A1),DAY (A1)),"d") While charactercount is not an absolute metric, I note thatthis formula is theshortest 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 thecharactercount later. Rick |
#24
|
|||
|
|||
Days to next anniversary
Thanks, Biff
BTW...I wasn't trying to concoct the shortest possible formula. It's just that formulas that wrap to the next line always make me wince (It's probably just a harmless character flaw g). Anyway, I usually stop tweaking if it gets to one line. *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: 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 |
#25
|
|||
|
|||
Days to next anniversary
Hi Ron
Don't know what was going on with my previous replies getting screwed up. Yes is does work for UK, provided you switch the month and day around =IF(A1,LOOKUP(365,((TEXT(A1,"d/m/")&YEAR(TODAY())+{0,1})-TODAY())),"") -- Regards Roger Govier "Ron Coderre" wrote in message ... Thanks, Roger and, very true about the date format....But, I had it in the back of my mind that the formula could easily be tweaked for non-US date formats. Hopefully, that holds true. *********** Best Regards, Ron XL2002, WinXP |
#26
|
|||
|
|||
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.) Nope, unfortunately your "thought" was correct... this formula, and your and Biff's subsequent modifications to it, do get tripped up by a start date of 02/29/2000 (or any February 29th in a proper leap year)... it happens whenever the "TODAY" date is anywhere within the range of January 1st to February 28th. As it turns out, ALL of the formulas submitted so far (including Ron's and its variations) will produce an incorrect calculation if the TODAY date is anywhere within the range of January 1st to February 28th of a NON leap year and the start date is any day on or after March 1st of a leap year... under those conditions, the date difference will be one day less than it is supposed to be. To test what I am saying, try a Start Date of March 1, 2000 and a TODAY date of February 27, 2007. There should be 3 days difference between these days (Feb 28, Feb 29 and Mar 1), but all formulas report 2 days... and this missing day is carried forward for Start Dates up to and including December 31, 2007. Rick |
#27
|
|||
|
|||
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. Nope, it is not starting to look good at all... this formula, and Ron's original, also get tripped up by a start date of 02/29/2000 (or any February 29th in a proper leap year)... it happens whenever the "TODAY" date is anywhere within the range of January 1st to February 28th. Unfortunately, that is the least of the problems... As it turns out, ALL of the formulas submitted so far (including Ron's and its variations) will produce an incorrect calculation if the TODAY date is anywhere within the range of January 1st to February 28th of a NON leap year and the start date is any day on or after March 1st of a leap year... under those conditions, the date difference will be one day less than it is supposed to be. To test what I am saying, try a Start Date of March 1, 2000 and a TODAY date of February 27, 2007. There should be 3 days difference between these days (Feb 28, Feb 29 and Mar 1), but all formulas report 2 days... and this missing day is carried forward for Start Dates up to and including December 31, 2007. Rick |
#28
|
|||
|
|||
Days to next anniversary
Don't know what was going on with my previous replies getting
screwed up. Yeah, that was strange. Yes is does work for UK, provided you switch the month and day around =IF(A1,LOOKUP(365,((TEXT(A1,"d/m/")&YEAR(TODAY())+{0,1})-TODAY())),"") Actually, it won't work properly as there is a flaw in it, and in ALL of the other formulas as well... see either of my two latest postings for the explanation. Rick |
#29
|
|||
|
|||
Days to next anniversary
Perhaps I'm being dense....but....
Hire Date: 01-MAR-2000 Today : 27-FEB-2007 Per the formula: 2 days until the next anniversary date The math: Hire Date Anniversary: 01-MAR-2007 Less: Today's date : 27-FEB-2007 Equals : 2 days Can you clarify what you discovered? *********** Regards, Ron XL2002, WinXP "Rick Rothstein (MVP - VB)" wrote: 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. Nope, it is not starting to look good at all... this formula, and Ron's original, also get tripped up by a start date of 02/29/2000 (or any February 29th in a proper leap year)... it happens whenever the "TODAY" date is anywhere within the range of January 1st to February 28th. Unfortunately, that is the least of the problems... As it turns out, ALL of the formulas submitted so far (including Ron's and its variations) will produce an incorrect calculation if the TODAY date is anywhere within the range of January 1st to February 28th of a NON leap year and the start date is any day on or after March 1st of a leap year... under those conditions, the date difference will be one day less than it is supposed to be. To test what I am saying, try a Start Date of March 1, 2000 and a TODAY date of February 27, 2007. There should be 3 days difference between these days (Feb 28, Feb 29 and Mar 1), but all formulas report 2 days... and this missing day is carried forward for Start Dates up to and including December 31, 2007. Rick |
#30
|
|||
|
|||
Days to next anniversary
No, you are not dense... there is a problem, but it is not year-long like I
first thought... it occurs for a start date of February 29th of a leap year with a TODAY date between January 1st and February 28th of a non leap year. Hire Date: 29-FEB-2004 (or any leap year) Today: 27-FEB-2007 (or any non leap year) Calculated Difference = 2 days Hire Date: 1-MAR-2004 (or any leap year) Today: 27-FEB-2007 (or any non leap year) Calculated Difference = 2 days Both Hire dates produce the same difference. I mistakenly thought that carried throughout the year, but the problem actually is how to handle the collapsing of the leap day into years where that date does not exist. Rick "Ron Coderre" wrote in message ... Perhaps I'm being dense....but.... Hire Date: 01-MAR-2000 Today : 27-FEB-2007 Per the formula: 2 days until the next anniversary date The math: Hire Date Anniversary: 01-MAR-2007 Less: Today's date : 27-FEB-2007 Equals : 2 days Can you clarify what you discovered? *********** Regards, Ron XL2002, WinXP "Rick Rothstein (MVP - VB)" wrote: 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. Nope, it is not starting to look good at all... this formula, and Ron's original, also get tripped up by a start date of 02/29/2000 (or any February 29th in a proper leap year)... it happens whenever the "TODAY" date is anywhere within the range of January 1st to February 28th. Unfortunately, that is the least of the problems... As it turns out, ALL of the formulas submitted so far (including Ron's and its variations) will produce an incorrect calculation if the TODAY date is anywhere within the range of January 1st to February 28th of a NON leap year and the start date is any day on or after March 1st of a leap year... under those conditions, the date difference will be one day less than it is supposed to be. To test what I am saying, try a Start Date of March 1, 2000 and a TODAY date of February 27, 2007. There should be 3 days difference between these days (Feb 28, Feb 29 and Mar 1), but all formulas report 2 days... and this missing day is carried forward for Start Dates up to and including December 31, 2007. Rick |
Thread Tools | |
Display Modes | |
|
|