Calculating a date

[Fridolin]

Hi!

Asked before but no luck, so here we go again. Is it possible to calculate a date from the following three field: year, week (fiscal) and day type (1 to 7)? The other way around is no problem so it should work. But how (feeling dumb)?

Thanks in advance!!!

[Ugo DI LUCA]

Hi,

Try :

WeekOfYearFiscal * 7 + Date(mS; dS; Year) - DayOfWeek(Date(mS; dS; Year))-(7-DayNumber)

with mS equal to Starting Month of Fiscal Period and dS equal to Starting Date of Fiscal Period

[transpower]

I don't know an elegant way to to this, but a brute force technique would be to loop over the WeekofYearFiscal function until you get the proper week. Then loop over the dates of that week using DayofWeek until you get the proper day. Then use the Date function to combine the month, day, and year.

[RalphL]

I think you need another field. You need to know the starting day of the fiscal year. Does the fiscal year star on a Sunday or a Monday or someother day? I am assuming that the day type is for the date to be calculated.

[Ugo DI LUCA]

Hi Ralph,

This one wouldn't return any value if either the dayNumber and Week(Fiscal) selected were giving results not including within the Fiscal Year.

Case(WeekofYearFiscal * 7 + Date(mS, dS, Year) - DayofWeek(DATE(mS, dS,Year))-(7-DayNumber)

< DATE(mS,dS,Year),TextToDate(""),

Case(WeekOfYearFiscal * 7 + DATE(mS, dS, Year) - DayofWeek(DATE(mS, dS,Year))-(7-DayNumber)

>DATE(mS,dS,Year),TextToDate(""),

WeekOfYearFiscal * 7 + DATE(mS, dS, Year) - DayofWeek(DATE(mS, dS, Year))-(7-DayNumber)))

With :

WeekOfYearFiscal being your Week (fiscal) input

Year being your Year Input

DayNumber being your Day Input (1 to 7)** may be some validation needed here for the input

mS = Starting Month of Fiscal Year (number)

dS = Starting Date of Fiscal Year (number)

The calc above was made with FM5.5 but would work with 7 too, when you'd have changed the Function name and separators.

It may BTW be interresting to use a Custom Functions here.

[Ugo DI LUCA]

And a text result calc, with the error checkings needed.

Case((DayNumber-7)<= 0 and not DayNumber<0,

Case(WeekofYearFiscal * 7 + Date(mS, dS, Year) - DayofWeek(DATE(mS, dS,Year))-(7-DayNumber)

<DATE(mS,dS,Year),"Error-WeekNumber",

Case(WeekOfYearFiscal * 7 + DATE(mS, dS, Year) - DayofWeek(DATE(mS, dS,Year))-(7-DayNumber)

>DATE(mS,dS,Year+1)-1,"Error-WeekNumber",

DateToText(WeekOfYearFiscal * 7 + DATE(mS, dS, Year) - DayofWeek(DATE(mS, dS, Year))-(7-DayNumber)))),

"Error-DayNumber")

PS : The calc in my last post should also be revisited

Case(WeekofYearFiscal * 7 + Date(mS, dS, Year) - DayofWeek(DATE(mS, dS,Year))-(7-DayNumber)

< DATE(mS,dS,Year),TextToDate(""),

Case(WeekOfYearFiscal * 7 + DATE(mS, dS, Year) - DayofWeek(DATE(mS, dS,Year))-(7-DayNumber)

>DATE(mS,dS,Year+1)-1,TextToDate(""),

WeekOfYearFiscal * 7 + DATE(mS, dS, Year) - DayofWeek(DATE(mS, dS, Year))-(7-DayNumber)))

[RalphL]

Hi Ugo,

If you are using FMP's WeekofYearFiscal to get the week number then it is based on the following from FMP Help:

"Returns a number between 1 and 53 representing the week containing date, figured according to startingDay. startingDay indicates which day is considered the first day of the week.

The first week of the year is the first week that contains four or more days of that year. For example, if you select 1 (Sunday) as the starting day, then January 1 must be on Sunday, Monday, Tuesday, or Wednesday for that week to be the first week of the fiscal year. If you select 2 (Monday) as the starting day, then January 1 must be on Monday, Tuesday, Wednesday, or Thursday for that week to be the first week of the fiscal year.

It is possible, using this function, that dates in a particular year will be returned as the 53rd week of the previous year. For example, if in 2003 you selected Sunday (1) as the starting date, then January 1, 2, or 3 in 2004 would occur in week 53 of fiscal year 2003 (in 2004, January 1 is on a Thursday). The first day of fiscal year 2004 would be on Sunday, January 4, because you selected Sunday (1) as the starting day."

So then the starting month is always 1. The starting date depends on the day of 1 January and the starting day.

[Ugo DI LUCA]

Hi Ralph,

Thanks. Never used the Fiscal Year myself , as my Fiscal Year doesn't start on January.

I'll check back.

[Ugo DI LUCA]

May be I finally understood the point then....

Without using the WeekOfFiscalYear ( )

FirstDayOfFiscalYear (Date) =

Date(1;1;Year)-Mod(DayOfWeek( Date(1;1;Year) )+7-N;7)

+

Case((1+Mod(6+N+2;7) >= DayOfWeek( Date(1;1;Year) ) )

Or (Year(Date(1;1;Year)-Mod(DayOfWeek ( Date(1;1;Year) )+7-N;7))<Year);7)

Where N = Starting Day Number of fiscal year

Then :

DayPick =

Case(weekFiscal * 7 + GetAsDate (FirstDayOfFiscalYear ) -

DayOfWeek ( GetAsDate (FirstDayOfFiscalYear ))-(7-DayNumber)>GetAsDate (FirstDayOfFiscalYear ) ;weekFiscal * 7 + GetAsDate (FirstDayOfFiscalYear ) -

DayOfWeek ( GetAsDate (FirstDayOfFiscalYear ))-(7-DayNumber);

GetAsDate ( "" ))

with your Original parameters, that is

weekFiscal, a number from 1 to 53

DayNumber, a number from 1 to 7

Year (number)

and N = Starting Day Number of Fiscal Year, as pointed by Ralph.

Now I'm unsure what result would be expected when you'd have :

DayNumber = 1 (Sunday)

weekFiscal = 1

N = 2 (Fiscal Year Starting on Monday)

Year = 1996

In this case, the FirstDayOfFiscalYear was Monday Jan. 7, so I concluded there was no Sunday in that first week of Year, which is the reason for the final GetAsDate("")

You can combine both calcs or use a custom function...and may be simplify this calc which seems terribly complicated !

[Fridolin]

Thanks a lot!

You guys are really helpful.

Fridolin