3 Replies Latest reply: Apr 5, 2013 11:23 AM by Donald Hutchins

# Financial Calendar Development with Hours

Hi guys,

The financial calendar 2011/ 2012 starts from 01.10.2011 at 6 Hrs and ends on 01.10.2012 at 6 Hrs. The calendar must be always with Hours starting from 6 hrs in the morning to 5 everyday

Can any body help me create the calendar? I am getting from 01.10.2011 6 Hrs to 31.09.2011. But I need until 01.10.2012 6 Hrs.

Attached is my Application

• ###### Re: Financial Calendar Development with Hours

Have a look at the attached.

• ###### Re: Financial Calendar Development with Hours

Hi Donald,

one question. Instead of using Tempdate including Year, Month, Day, Hour is it possible to produce financial year using Calendaryear, Calendarmonth, CalendarDay, Calendarhour?

Struggling to get it...

Sravan

• ###### Re: Financial Calendar Development with Hours

You could do this in a preceding LOAD this is just before your main load (for Date_NUM, Hour, Day, etc.) and just after the first load (for HoursForDisplay, etc.).  Then just change all the references in the FinancialYear calculation formula to refer to Calendarmonth, Calendarday, etc.  Something like this:

```
Calendar:
*,
dual(num(Hour) & ' - ' & num(Hour+1),num(Calendarhour))   as HourforDisplay,
dual(num(Calendarhour) & ' - ' & num(Calendarhour+1),num(Calendarhour))   as CalendarHourforDisplay;
*,
If( num(Calendarmonth) = 10,
if( Calendarday = 1,
if( Hour > 5,
'FY ' & Right(CalendarYear,2)  &'/'& Right(CalendarYear+1,2),
'FY ' & Right(CalendarYear-1,2)  &'/'& Right(CalendarYear,2)
),
'FY ' & Right(CalendarYear,2)  &'/'& Right(CalendarYear+1,2)
),
if( num(Calendarmonth) > 10,
'FY ' & Right(CalendarYear,2)  &'/'& Right(CalendarYear+1,2),
'FY ' & Right(CalendarYear-1,2)  &'/'& Right(CalendarYear,2)
)
) As FinancialYear;
Date(TempDate,'YYYYMMDD')& Hour as Date_NUM, //DateHour,
Hour,

num(Day(TempDate),'00')   as Day,
Month(TempDate)     as Month,
'Q' & Ceil(Month(TempDate)/3)  as Quartal,
Year(TempDate)      as Year,

//###NormalCalenderdata###
Year( ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as CalendarYear,
Month( ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as Calendarmonth,
Day(  ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as Calendarday,
Hour(  ConvertToLocalTime(Year(TempDate) &'-'& num(Month(TempDate),'00') &'-'& num(Day(TempDate),'00') &' '& maketime(num#(Hour)),'UTC-06:00',0)) as Calendarhour

resident TempCalendar
order by TempDate ASC;
```

That said, if you do just this, your result will be a bit off because you are converting Calendarmonth, etc. to local time based on a 6 hour GMT offset. But other references, including the HOUR, are not localized like this.  If you want to convert to a local time, then perhaps you should consider converting in the beginning, so that all references are consistent.  Examine your table for example to see the differnces between your Date_NUM values and the corresponding CalendarXXX fields.

So if the Financial/Fiscal date calc is to be localized using ConvertToLocalTime, then do this in the beginning, something like this:

```FOR i = 0 to 23  // Hour 0 to 23
TempCalendar: