	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: Calculating the Day Number in the Year

This useful function comes from Charliy Nash. This function can calculate the day number in the year. This can be useful in payroll or other date-based systems

Function (datevar tdate)
// DOY - Day of Year
// INPUT: Date to be evaluated
// OUTPUT: Julian day of the year
// Example: DOY(11–Feb-2003) = 42 (tdate - date(year(tdate),1,1))+1 ;

You can also calculate business days. If you look carefully at Seagate Info 7, you'll discovered the IsBusinessDay function is available in the formula editor. But it is no longer available in CR8. We did however, find in the help for DateDiff, a method for calculating the number of business days between two dates.

Local DateTimeVar d1 := {Orders.Order Date};

Local DateTimeVar d2 := {Orders.Ship Date};

DateDiff ("d", d1, d2) -

DateDiff ("ww", d1, d2, crSaturday) -

DateDiff ("ww", d1, d2, crSunday)

The local variables are just there to make the rest of the formula easier to read. The first DateDiff function calculates the number of days between our two dates (as in the beginner hint above), while the next two DateDiff functions subtract the Saturdays and Sundays.

But what if you want to calculate the week number?

A customer wanted to identify the week number of a dated transaction. This year January 1 is a Sunday, so it’s quite easy to calculate the days since Jan one and divide by 7. But what if you wanted the matching week number in a different year?

The "DatePart" function has some really powerful features here.

DatePart("ww",{Table.DateField},crSunday) returns the week number and assumes the week starts on a Sunday. Use different day of the week constants (crMonday, crTuesday, etc) if your weeks start on a different day. If you omit the day of week constant then Crystal assumes Sunday is the start of the week.

A variation on this is to use First Week of Year constants as in DatePart("ww",{Table.DateField},crFirstJan1). This sets the rule for when the first week of the year is calculated. Valid values are:

crUseSystem – use the system setting
crFirstJan1 – start with the week with January the 1^{st
in it.}
crFirstFourDays – start with the first week that has at least four days in the new year
crFirstFullWeek – start with the first complete week.

How to calculate a Week Number using a Formula

What happens if your accounting year doesn’t start on the 1^st of January? In New Zealand, most companies start their financial year on 01 April. We know of several companies who start their year on 1 July. In New Zealand government, departments often use a July to June financial year.

Part One: Calculate the start of the financial year {@FY Start}

The first formula you need is to calculate the start of the financial year based on a date field in your transaction table. Here is a formula for an April to March Financial Year.

If month({table.datefield}) <= 3 then // Transaction is in Jan-Mar

Date(year({table.datefield}) -1 , 4,1)

Else Date(year({table.datefield}) , 4,1)

If your year starts in July, use this variation of the formula.

If month({table.datefield}) <= 6 then // Transaction is in Jan-Jun

Date(year({table.datefield}) -1 , 7,1)

Else Date(year({table.datefield}) , 7,1)

Part Two: Decide when the first week starts {@Week Start}

Now we need to calculate when the first week starts. This example calculates the first Saturday of the financial year, and then assumes the first incomplete week is Week One. You can adjust this if your week starts on another day or if Week One is calculated differently – see later examples.

If DayofWeek({@FY Start}) =1 then {@FY Start} //Already Starts on a Sunday

Else {@FY Start} - DayofWeek({FY Start}) + 1

Part Three: Calculate when the transaction week starts {@Transaction Week}

Now we need to calculate the when the week of the transaction record starts. This example calculates the prior Sunday of each record.

If DayofWeek({table.datefield}) =1 then {table.datefield} // Already Sunday

Else { table.datefield } - DayofWeek({table.datefield }) + 1

Part Four: Calculate the week number

All you need to do now is to subtract those two numbers. They will both always refer to a Sunday, so therefore will always be a multiple of seven days apart.

({@Transaction Week} – {@Week Start}) / 7

Part Five: Decide when the first week starts {@Week Start}
(Saturday or Monday)

The Crystal Reports default working week starts on a Sunday, and all of the previous sample formulas assume that day. However, there could be a good reason to start the week on a Saturday or a Monday.

The first formula from last month {@FY Start}, calculated the start of the financial year. It needs no changes and works as required.

Now we need to calculate the when the first week starts on a Saturday. This example calculates the first Saturday of the financial year, and then assumes the first incomplete week is Week One.

If DayofWeek({@FY Start}) =7 then {@FY Start} ‘ Already a Saturday

Else {@FY Start} - DayofWeek({@FY Start})

If your week starts on a Monday, use this variation of the formula.

If DayofWeek({@FY Start}) =1 then {@FY Start}-6 ‘ Sunday-go back

Else

If DayofWeek({@FY Start}) =2 then {@FY Start} ‘ Already a Monday

Else {@FY Start} - DayofWeek({FY Start}) + 2

Calculate when the transaction week starts {@Transaction Week}

Use a similar formula to calculate the start of the week for the transaction date. Use this formula for the Saturday start of week.

If DayofWeek({table.datefield}) =7

then {table.datefield} ‘ Already a Saturday

Else {table.datefield} - DayofWeek({table.datefield})

And use this one for the Monday week based formula

If DayofWeek({table.datefield}) =1

then {table.datefield}-6 ‘ Sunday-go back

Else If DayofWeek({table.datefield}) =2

then {table.datefield} ‘ Already a Monday

Else {table.datefield} - DayofWeek({table.datefield}) + 2

The formula to calculate the week number is the same as last month. Just subtract those two dates. They will both always refer to a Sunday, so therefore will always be a multiple of 7 days apart.

({@Transaction Week} – {@Week Start}) / 7

And last, but not least, you can calculate the month value. We developed a series of useful formulas to get handy date values. This first formula calculates the last day of the month.

Local numbervar m:=month(CurrentDate) + 1;

Local numbervar y:=year(CurrentDate);

If m>12 then (m:=m-12; y:=y + 1);

Date(y,m,1)-1;

Once you have that formula, then it is quite easy to calculate the number of days in the month

Day({@LastDayofMonth)

From there, the Last Friday of the month is calculated as follows.

If DayofWeek({@lastDayofMonth}) >=6 then // Friday or Saturday

Then {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) + 6

Else {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) – 1

If you require a different last day of the month, create a variation of the formula below. This example calculates the Last Wednesday of this Month

If DayofWeek({@lastDayofMonth}) >=4 then // Friday or Saturday

Then {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) + 4

Else {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) – 3

Use these directly with your database fields or modify the formulas to make them a custom function (version 9 and above).

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.