By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,365 Online
Bytes IT Community
+ 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!!!

Presto731
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
Share this Question
Share on Google+
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

Presto731
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

Presto731
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

Presto731
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

missinglinq
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
  1. If Day(Date) = Day(JoinDate) Then
  2.   'Payment is due today
  3.   'Code goes here 
  4. End If 
Good Luck!

Linq ;0)>
Jul 19 '07 #7

Presto731
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
  1. If Day(Date) = Day(JoinDate) Then
  2.   'Payment is due today
  3.   'Code goes here 
  4. End If 
Good Luck!

Linq ;0)>
Jul 19 '07 #8

missinglinq
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

missinglinq
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

Presto731
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

missinglinq
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

Post your reply

Sign in to post your reply or Sign up for a free account.