Number of Days in Month from a Text date field

[cremore]

I have a CSV data source that holds the month and year in separate numeric fields.

Does anyone know a formula that can get me the number of days in the month. I keep running into difficulties with data types (Numberic, text Date) when trying to construct the formula

(I can do this in impromptu or MS Access no problem, but I don't want to add another layer between the csv and transformer if I can avoid it)

Thanks

[almeids]

I'm not aware of any formula to do that...but will think about it.
If you're not worried about weekends or leap years, you can do it as a simple conditional calculation based on the month number.

[cremore]

Thanks - that's what i've done as a quick fix - multiple nested if statements, would like something cleaner though

[almeids]

I'm guessing you've already been down the path of trying to convert your year/month into a date?  I'm trying to do that with a data source containing year and month as strings and am reminded of why I've always HATED Transformer's expression editor.

If you are able to produce a date from your data items, then:
a) Post your formula here!  I'm pulling my hair out.
b) You can probably handle leap years with a formula that subtracts days-from-1900-to-first-day-of-month from days-from-1900-to-last-day-of-month (plus 1!).

I can't think of any way you're going to be able to handle working days with the limited function set in Transformer, there's not even anything to determine day of week (not that it would help with monthly granularity).

[cremore]

The Company I work for has a 31 March Financial year end hence the added complication. My original post was based on the date below being as test file. However I define FISCPER3 & FISCYEAR as numeric when bring in from the CSV. Here's the formual for creating a date from the year and period

Data class:Text
Formula: number-to-string ( if ( "FISCPER3" < 10 ) then ( "FISCYEAR" - 1 ) else ( "FISCYEAR" ) ) + right ( '00' + number-to-string ( if ( "FISCPER3" < 10 ) then ( "FISCPER3" + 3 ) else ( "FISCPER3" - 9 ) ) , 2 ) + '01'


As a quick fix I did an if statement to calc the no of days in month: (with march year end Fiscal year 2008 period 011 is Feb!)
if ( "FISCPER3" = 001 ) then ( 30 ) else ( if ( "FISCPER3" = 003 ) then ( 30 ) else ( if ( "FISCPER3" = 006 ) then ( 30 ) else ( if ( "FISCPER3" = 008 ) then ( 30 ) else ( if ( "FISCPER3" = 011 ) then ( if ( "Year/period" = '2008011' ) then ( 29 ) else ( 28 ) ) else ( 31 ) ) ) ) )

[almeids]

Your offset fiscal year should not have any impact on a number-of-days calculation, each month still has the same number of days regardless.
Thanks for the calc...however you indicated that it's a Text item, and I was wondering how to calculate a Date.  It seems impossible...the syntax for a hardcoded date does not include quotes so it isn't just a string interpreted as a Date, and I don't see any functions to convert to a Date from a string, string components, or integer components.
Given this, the best you may be able to do is some sort of MOD 4 calculation to determine if the year is a leap year.  Either that, or bite the bullet and move your logic upstream to Impromptu.
Good luck.