Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculating # of months between year-month variable and date-time field
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Calculating # of months between year-month variable and date-time field
 Login/Join
 
Platinum Member
posted
I thought this would be fairly simple, but I have tried many approaches and cannot get this to work correctly.

The user inputs the month number and 4 digit year into 2 variables. This is the end date. The start date is a date-time field with a format of HYYMDs.

I need to create a field that calculates the difference in months between the two dates.

How is this done?

Thank you.

This message has been edited. Last edited by: JohnB,


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
There are several ways to get this done
The best way is by the use of so called smart dates

Once you have two smartdates you have some formulas to calculate the difference in days, weeks, months, years , business days .
Step one convert a yymds format to a YYMD format
SDATE/YYMD=HDATE(startdate,'ymd');
Step two concatenation the input year month to a string holding something like 20120701
tdate/A8YYMD='&yy'||'&mm'||'01';
Step 3 convert the string to a smartdate.
EDATE/YYMD=tdate;
DIFF/I2=DATEDIFF(EDATE,SDATE,'m'); for month difference

Btw this should all be in the manual or the on line help files

Nb there might be a typo in the formulas since I do not have the manuals ad hand.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I thought it should be simple like your example.

Thank you.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Another question on DATEDIF.

When working with 2 smart dates, and the start date could be within the last few years and the end date is in greater than or equal to the start date DATEDIF works fine. DATEDIF also works fine if end date is earlier the start date (this was a test). In this case the number of months between the 2 dates is a negative number

Many times the end date is 01/01/1900 and DATEDIF gives a number months diference in the thousands, which is incorrect. There's not over 10,000 months beween 01/01/1900 and 01/01/2013.

Why does DATEDIF do this?

Thank you.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If your input variables for month and year are empty, it probably will give that date

If you want to test it, you might start with typing the result and see what you get
A next step could be to evaluate the entered values and if the year is less then a certain value you loop back and ask for a correct value.
You also might add a default value of let's say today in those fields.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
In this case, Frank, the dates are from the data file. The end date in the data file may times has a value of 01/01/1900.

So if a date field is empty it will display a value of 01/01/1900?

If that's the case, then it should be easy to get DATEDIF to work, by adding a little extra coding.

Thank you.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculating # of months between year-month variable and date-time field

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.