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.
12  2866 
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!?
I'll try to fiddle with it. Let me know if you have any other revalations. Thank you Mike
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?
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
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
Day(Date) returns the day of the of the month, i.e. 19 for 7/19/07. So, something like - If Day(Date) = Day(JoinDate) Then
-
'Payment is due today
-
'Code goes here
-
End If
Good Luck!
Linq ;0)>
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
- If Day(Date) = Day(JoinDate) Then
-
'Payment is due today
-
'Code goes here
-
End If
Good Luck!
Linq ;0)>
Glad we could help!
Linq ;0)>
Hi Presto731
I think your definition of anniversary and mine are a different!
MTB
That kind of threw me at first too, Mike! But monthly anniversary has come into common use of late, sad to say!
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! ;)
You know, there's got to be an actual word; wish I could find it!
;0)> Sign in to post your reply or Sign up for a free account.
Similar topics
by: tertius |
last post by:
Hi,
Is there a more pythonic way to write or do a date difference
calculation? I have as input two date fields in the form 'YYYY-MM-DD'
TIA
Terius
|
by: mattmerc |
last post by:
Hi,
I would like to have a date_last_modified field for one of my SQL
tables. Instead of coding my front end to keep up with this field is
there a way to user a formula for this column in...
|
by: Lapchien |
last post by:
My table has a record called date/time - filled (not surprisingly) with a
date and time. I can format the date in one query field, also the time,
using format in an expression. My user wants me...
|
by: A |
last post by:
Hi Group,
Coming from a VB background I am looking for the quickest path to do a date
diff in C#. I see that C# has no date diff function so this must be more
obvious than it looks.
Thanks
|
by: Raghu Raman |
last post by:
Hi
I want to find the difference between 2 dates in terms of year.It is
confusng in msdn.could u help me on that.
for ex,
int year=datediff("y",date1,date2)//of course it is in VB.
...
|
by: dkrysmann |
last post by:
Hi,
I'm very new to this group and the php, now I've question about date
function.
I need function how to calculate time diffrence between two dates.
First date: $rs['lastBuildDate' is like...
|
by: Troy Lee |
last post by:
I have a report in Access. I want to calculate with the DateDiff
function. Two questions.
1. How do I set up the Date Diff to subtract a date in the past from
today's date? I want this to be...
|
by: Pauley |
last post by:
Hello,
Please keep in mind when you read this post that I am a total noob when it comes to coding.
Coding in ASP
On MS Server
SQL Database/Table
I know this topic has been covered many...
|
by: sajitk |
last post by:
Dear All
I have two dates ile Start Date and End Date.
I want to get the difference of the 2 dates in months.....How do we do it...
For eg.
Start Date = 01/11/2009
End Date:...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |
