Let's Get Social!
Focal Point    Focal Point Forums    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
Platinum Member
 posted February 02, 2013 08:19 AM
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, 2006 IP
Virtuoso
 posted February 02, 2013 03:15 PM 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,IE7test: WF 7.6.10 on the same platform and databases,IE7

 Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006 IP
Platinum Member
 posted February 03, 2013 01:12 PM 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, 2006 IP
Platinum Member
 posted February 03, 2013 02:46 PM 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, 2006 IP
Virtuoso
 posted February 04, 2013 07:29 AM 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,IE7test: WF 7.6.10 on the same platform and databases,IE7

 Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006 IP
Platinum Member
 posted February 09, 2013 12:49 PM 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, 2006 IP