Days360

[jtm311]

Hello I'm trying to reproduce a function from Excel.

=DAYS360(date_Field1,Date_Field2)/(30)

I've tried the months() however, no luck.

This is will be used in the client side of a dialog
Thanks

[jtm311]

Re: Days360

Anyone?
Here is the description.

The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

[agillbb]

Re: Days360

You will probably want to us Javascript in the clientside.
Personally, because date calculations can be messy in javascript, I simply include the DateJS library (http://www.datejs.com/) in my grid and in one or two lines, I have the date calculation I need.
It's open source and easy as ever. Just download the date.js file, include it in your project, then add it to your grid - 30 seconds of work.
Andy.

[Keith Hubert]

Re: Days360

John,

Is your user entering the first date and you want to calculate the date, which will be date entered plus 360?

[Davidk]

Re: Days360

Probably not at all helpful, but how about actually using the Excel function Days360. Excel has to be around, but usually is. I found this call and adjusted it a bit. In your Grid, in Xbasic Functions, create a function as follows...

Function D360(begin_date, end_date)
xlApp = ole.create("excel.Application.14")
D360=xlApp.WorksheetFunction.Days360(begin_date, end_date)
end function

To find out what version of excel you have, get into the Alpha Control Panel, View, OLE Automation Browser, Category "All Objects", filter on "Excel" and then click on Microsoft Excel Application. A description will be displayed giving you the program identifier... as used above.

Now, in any Ajax Callback, or Server side event, you can call the D360 function... e.g. in the Server-side event OnTitlesRender,

Dim d1 as c
Dim d2 as c

d1 = "01/01/2012"
d2 = "01/12/2012"

d3 = Val(D360(d1, d2)) / 30

e.javascript = "alert('" + d3 + "');"

As said, probably not what you want, but cool that it works.

[jtm311]

Re: Days360

John,

Is your user entering the first date and you want to calculate the date, which will be date entered plus 360?

No both dates are loaded from a DBF. In the end I need to know the number of months between two dates.

[iRadiate]

Re: Days360

John,

There is a bus_days_between() function that you might be able to creatively massage. The thing is this function omits weekend days.

? bus_days_between({01/01/2012},date())

= 97

More on the wiki here ... http://wiki.alphasoftware.com/BUS_DAYS_BETWEEN+Function

Stephen

[jtm311]

Re: Days360

John,

There is a bus_days_between() function that you might be able to creatively massage. The thing is this function omits weekend days.

? bus_days_between({01/01/2012},date())

= 97

More on the wiki here ... http://wiki.alphasoftware.com/BUS_DAYS_BETWEEN+Function

Stephen

Thanks this one works in my interactive window, however, not in the client side.

?month(ctod("05/21/2012")) - month_number("01/01/2012")
= 5

[jtm311]

Re: Days360

^bump Anyone on how to make this work on the client side.

?month(ctod("05/21/2012")) - month_number("01/01/2012")
= 5

[Davidk]

Re: Days360

You can't, specifically, if that's what you mean to do... since it's all Xbasic functions. You can perform an Ajax Callback and get it done, however.

[jtm311]

Re: Days360

You can't, specifically, if that's what you mean to do... since it's all Xbasic functions. You can perform an Ajax Callback and get it done, however.

Thanks David would you be able to offer a little more on how to perform the callback to make it work?
Thanks
John

[Davidk]

Re: Days360

This'll help. Attached is a dialog component. Unzip it into the folder where your other components are... then just click on A5W Pages and then back on Web Components to refresh the list.

The dialog is a good example because it doesn't involve any tables and so is easier to attach. The principle is exactly the same for grids.

The dialog has 3 fields and a button. The fields are Start Date, End Date and Months. The button performs an Ajax Callback into a function that runs your Xbasic code.

For example, you'd enter, or pick, 01/01/2012 for the Start Date and 21/05/2012 for the End Date and then click the button.

Months will be filled with the calc result.

In the Dialog, have a look at the XBasic functions - left side of the dialog. In there is a function named "days360"

Now have a look at the onClick event of the button. Here you'll find 1 entry - Action Javascript - an Ajax Callback. Edit this and you'll see how the call to the function "days360" is set up.

Most importantly, while you're editing the Action Javascript, have a look at the bottom of that pop-up form - there are 2 links - one for creating a function prototype using the name you typed into the Function name section. Have a look at the prototype, and notice that you can copy that code to the clipboard. The 2nd link is to open the Xbasic function directly from this edit screen... where you can paste in the prototype and then add you're own code. Which is exactly what I did. Let me know if it makes sense.

[jtm311]

Re: Days360

This'll help. Attached is a dialog component. Unzip it into the folder where your other components are... then just click on A5W Pages and then back on Web Components to refresh the list.

The dialog is a good example because it doesn't involve any tables and so is easier to attach. The principle is exactly the same for grids.

The dialog has 3 fields and a button. The fields are Start Date, End Date and Months. The button performs an Ajax Callback into a function that runs your Xbasic code.

For example, you'd enter, or pick, 01/01/2012 for the Start Date and 21/05/2012 for the End Date and then click the button.

Months will be filled with the calc result.

In the Dialog, have a look at the XBasic functions - left side of the dialog. In there is a function named "days360"

Now have a look at the onClick event of the button. Here you'll find 1 entry - Action Javascript - an Ajax Callback. Edit this and you'll see how the call to the function "days360" is set up.

Most importantly, while you're editing the Action Javascript, have a look at the bottom of that pop-up form - there are 2 links - one for creating a function prototype using the name you typed into the Function name section. Have a look at the prototype, and notice that you can copy that code to the clipboard. The 2nd link is to open the Xbasic function directly from this edit screen... where you can paste in the prototype and then add you're own code. Which is exactly what I did. Let me know if it makes sense.

Thank you for taking the time so I can see how this works. I will give it a try tomorrow.

John

[agillbb]

Re: Days360

Hi John,

As I mentioned earlier, you could easily do it on the client-side if you wish. Either:

1) Use DateJS - lots of functions to do this with, or
2) Use plain old javascript - see http://jsfiddle.net/E5Bz3/1/ for a test case that should do what you need. The JS code looks like this: DisplayTo.getMonth() - DisplayFrom.getMonth() + (12 * (DisplayTo.getFullYear() - DisplayFrom.getFullYear())));

I think you should probably go with no.2.

If it was me, I would try to include a dummy field in my initial DB query called MonthsDiff for example, and somehow set this to the value you need. This final value would be available as a field in your grid.

Andy.

[jtm311]

Re: Days360

Hi John,

As I mentioned earlier, you could easily do it on the client-side if you wish. Either:

1) Use DateJS - lots of functions to do this with, or
2) Use plain old javascript - see http://jsfiddle.net/E5Bz3/1/ for a test case that should do what you need. The JS code looks like this: DisplayTo.getMonth() - DisplayFrom.getMonth() + (12 * (DisplayTo.getFullYear() - DisplayFrom.getFullYear())));

I think you should probably go with no.2.

If it was me, I would try to include a dummy field in my initial DB query called MonthsDiff for example, and somehow set this to the value you need. This final value would be available as a field in your grid.

Andy.

Andy thanks I did try it for some reason it was giving more problems, but thanks for the follow-up. I do like the features of the DateJS.
John