6 Replies Latest reply on Dec 6, 2011 4:29 PM by shogun

# Elapsed time [weekdays only] in weeks based on current date.

### Title

Elapsed time [weekdays only] in weeks based on current date.

### Post

I'm looking for help with a calculation that returns the elapsed time in weeks based on the current date.

Example: PrepStartDate is 12/1/2011, today's date is 12/5/2011 result is .6.  Each day of the week is .2. One week is 5 x.2 or 1.

Below is an example of the calculation I use in my database that returns the total weeks based on a start and end date.

In addition to knowing the total time period, I would like to know how much time has elapsed based on the current date.

(5 * Int ( ( ProjectPrepEndDate - ProjectPrepStartDate ) / 7 ) +Middle ( "0012345501234544012343340123223491111234010012340" ; 7 * (DayOfWeek ( ProjectPrepStartDate ) - 1 ) + DayOfWeek ( ProjectPrepEndDate ) ; 1)+1)/5

Thank you,

shogun

• ###### 1. Re: Elapsed time [weekdays only] in weeks based on current date.

Your expression is based on  a KnowledgeBase article #5281, that was recently reported has having an error in it. It is now fixed. the '9' in that string is the wrong number. It should be a zero instead.

See this report for more: KB Article "number of weekdays between two dates" has a bug

Do you want a deciimal value showing a fraction of a week such that

3 weeks plus one work day = 3.2?

If so, dividing the number of workdays as produced by the above calculation you've posted by 5 should return the correct result.

• ###### 2. Re: Elapsed time [weekdays only] in weeks based on current date.

Thanks PhilModJunk, I'll make the fix.

Please advise on how I could adapt that calculation to work with a start date and current date?

• ###### 3. Re: Elapsed time [weekdays only] in weeks based on current date.

Put get ( currentdate ) in place of "ProjectPrepEndDate".

Updating the original calculation to take advantage of newer features and to correct the typo:

Let ( [ End = Get (CurrentDate ) ; Start = ProjectPrepStartDate ] ;
5 * Int ( ( End - Start ) / 7 ) +
Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( Start ) - 1 ) + DayOfWeek ( End ) ; 1)+1)/5
) // Let

and select "unstored" in storage options if you want this to update automatically with each new day.

• ###### 4. Re: Elapsed time [weekdays only] in weeks based on current date.

Thanks again PhilModJunk, however when I went to implement this in my solution, I received the following error [see attached].

Is something missing from the expression?

• ###### 5. Re: Elapsed time [weekdays only] in weeks based on current date.

We're missing a left Parenthesis.

Updating the original calculation to take advantage of newer features and to correct the typo:

Let ( [ End = Get (CurrentDate ) ; Start = ProjectPrepStartDate ] ;
( 5 * Int ( ( End - Start ) / 7 ) +
Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( Start ) - 1 ) + DayOfWeek ( End ) ; 1)+1)/5
) // Let

• ###### 6. Re: Elapsed time [weekdays only] in weeks based on current date.

That's it!  Thank you.