435,254 Members | 2,365 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

# Date Diff Formula Help!!!

 P: 53 I'm having a formulating dilemma that I hope anyone can help with. I need to do some reporting for a list of people so I can know there anniversary dates for charging them. However the way it works isn't just on fixed number of days. its always the next month on the same day of your intial date. For Example: If you became a customer on 1/2/07, your annivesary date would be 2/2/07, which is 31 days. But if you became a customer on 2/2/07, your anniversary date would be 3/2/07, whichh is 30 days. Since not every month has the same numbers of days, I'm stumped. How would I construct the formula so I could pull a list each day of who has hit their annivesary date. I can't seem to get a date diff formula that makes it happen. Can anyone help with this? It would be much appreciated. Jul 19 '07 #1
12 Replies

 Expert 100+ P: 634 I'm having a formulating dilemma that I hope anyone can help with. I need to do some reporting for a list of people so I can know there anniversary dates for charging them. However the way it works isn't just on fixed number of days. its always the next month on the same day of your intial date. For Example: If you became a customer on 1/2/07, your annivesary date would be 2/2/07, which is 31 days. But if you became a customer on 2/2/07, your anniversary date would be 3/2/07, whichh is 30 days. Since not every month has the same numbers of days, I'm stumped. How would I construct the formula so I could pull a list each day of who has hit their annivesary date. I can't seem to get a date diff formula that makes it happen. Can anyone help with this? It would be much appreciated. Anniversary date = DateSerial(Year(StartDate)+1,Month(StartDate)+1, Day(StartDate)) I suspect that is too simple, OK for year one but years 2, 3 ... ?? The DateSerial function has the solution, but I will let you sort that! DateSerial function is a very powerful/flexible function. I should look at the help file entry MTB edit I have assume buy 'anniversary' you do mean the next year, as opposed to the next month you have indicated!? Jul 19 '07 #2

 P: 53 I'll try to fiddle with it. Let me know if you have any other revalations. Thank you Mike Jul 19 '07 #3

 P: 53 The srial formula worked sort of. It will show me the people on the same day next month. Next question being, will I have to set up 12 separate fields for each month with a criteria that just equals the current days date, or can I get it to calculate it daily throughout the year? Jul 19 '07 #4

 Expert 100+ P: 634 The srial formula worked sort of. It will show me the people on the same day next month. Next question being, will I have to set up 12 separate fields for each month with a criteria that just equals the current days date, or can I get it to calculate it daily throughout the year? I am not sure what you requirement is. Given that to-days date is 19 July 2007(or Date()), then for each person starting on 2 Jan 2007 what actually are you trying to return/filter ? MTB Jul 19 '07 #5

 P: 53 We bill Monthly by the date the customer becomes active. So if 5 become active today,their anniversaries dates would be 8/19/07, 9/19/07, 12/19/07, 1/19/08, 2/19/08.........and continues monthly until they are no longer a customer. What I need is a report that can be pulled each day to show who needs to be charged on that day Jul 19 '07 #6

 Expert 2.5K+ P: 3,532 Day(Date) returns the day of the of the month, i.e. 19 for 7/19/07. So, something like Expand|Select|Wrap|Line Numbers If Day(Date) = Day(JoinDate) Then   'Payment is due today   'Code goes here  End If  Good Luck! Linq ;0)> Jul 19 '07 #7

 P: 53 I suppose I better get my VB skills up to snuff. Thanks Day(Date) returns the day of the of the month, i.e. 19 for 7/19/07. So, something like Expand|Select|Wrap|Line Numbers If Day(Date) = Day(JoinDate) Then   'Payment is due today   'Code goes here  End If  Good Luck! Linq ;0)> Jul 19 '07 #8

 Expert 2.5K+ P: 3,532 Glad we could help! Linq ;0)> Jul 19 '07 #9

 Expert 100+ P: 634 Hi Presto731 I think your definition of anniversary and mine are a different! MTB Jul 20 '07 #10

 Expert 2.5K+ P: 3,532 That kind of threw me at first too, Mike! But monthly anniversary has come into common use of late, sad to say! Jul 20 '07 #11

 P: 53 That kind of threw me at first too, Mike! But monthly anniversary has come into common use of late, sad to say! sorry fellas how about a Monthlyversary then! ;) Jul 20 '07 #12

 Expert 2.5K+ P: 3,532 You know, there's got to be an actual word; wish I could find it! ;0)> Jul 20 '07 #13