2 Replies Latest reply on Jun 4, 2012 12:44 PM by comment

# What's Wrong With this Date Calculation?

I have four fields, StartingDate, CurrentWeek, WeeksElapsed, and WeeksRemaining, which enable me to display the number of weeks elapsed this fiscal quarter and the number remaining.

StartingDate = [auto-enter data, developer manually sets date in field options. In this solution, StartingDate is 4/1/2012]

CurrentWeek = ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 1 ) & "..." & ( Get ( CurrentDate ) + 7 - DayOfWeek ( Get ( CurrentDate ) ) )

WeeksElapsed =

Let ( [ shift = 2 - DayOfWeek ( StartingDate ) ;

d1 = StartingDate + shift ;

d2a = Get(CurrentDate) - ( DayOfWeek ( Get(CurrentDate) ) = 7 ) - 2 * ( DayOfWeek ( Get(CurrentDate) ) = 1 ) ;

d2 = d2a + shift

] ;

Div ( d2 - d1 ; 7 ) + (Mod ( d2 - d1 ; 7 ) ≥ 2 )

) // let

WeeksRemaining = 13-z_WeeksElapsed

Apparently, my week counts are flipping on each Tuesday before the expected Sunday. So right now, for example, on Monday, 6/4, I see "week 9, 4 remaining," which is accurate. But tomorrow, I expect to see "week 10, 3 remaining," 5 days early.

Where did I go wrong?

• ###### 1. Re: What's Wrong With this Date Calculation?

Your complication looks (and is) quite complicated. Here's a simpler approach using DayofYear, with some bonus information thrown in.

As inferred from your last paragraph remark, the calculation gives the week which has fully ended, i.e. on Saturday it will 9, 4, and on Sunday 10,3. It should be easily adjustable for other requirements.

let (

[

startDate = "1.4.2012" ; // sample data

isToday = Get ( CurrentDate ) ; // control data

dayNumEndFiscalQ = DayOfYear ( date ( month ( startDate ) + 3 ; 0 ; year ( startDate ) ) ) ;

dayNumStart = DayOfYear ( startDate ) ;

dayNumToday = DayOfYear ( isToday ) ;

daysInFiscalQ = dayNumEndFiscalQ - dayNumStart + 1 ;

daysElapsed = dayNumToday - dayNumStart ;

weeksElapsed = Int ( daysElapsed / 7 ) ;

weeksRemaining = 13 - weeksElapsed

] ;

"Week " & weeksElapsed & ", " & weeksRemaining & " remaining" & " (days elapsed: " & daysElapsed & ", days remaining: " & daysInFiscalQ - daysElapsed

)

• ###### 2. Re: What's Wrong With this Date Calculation?

slraymond wrote:

the number of weeks elapsed this fiscal quarter

You need to define "elapsed week". See the help on WeekOfYear() and WeekOfYearFiscal() functions.

slraymond wrote:

StartingDate = [auto-enter data, developer manually sets date in field options. In this solution, StartingDate is 4/1/2012]

Is this ever going to change? If not, it would be more efficient to use either a one-record Preferences table or a global field, instead of replicating the same data in every record.