Basis for the Week Function (1 Viewer)

[khwaja]

I am calculating number of weeks between start and finish date. Some of the users have questioned the validity of calculation as they are not too sure if this is being calculated as Monday to Friday week or does it work by calculating 7 days. If the latter is true, then I should see a decimal value. I am using date diff formula.

 

[plog]

The code you are using counts the number of Sundays the timeframe you enter spans (excluding starting Sundays). For example:

DateDiff("ww","6/13/2015","6/14/2015")

Returns 1. Saturday-Sunday

And

DateDiff("ww","6/7/2015","6/13/2015")

Returns 0. Sunday - Saturday.

I don't know if that's what you want, but that's how the DateDiff works. This will never return a decimal. If you want it more granular, I suggest you count the difference in days.

 

[CJ_London]

suggest you test it - put some known dates into datdiff and see if it gives you what you expect.

You can find more about datediff and week here

https://msdn.microsoft.com/en-us/library/b5xbyt6f(v=vs.90).aspx

 

[khwaja]

Thanks a lot for both responses. Yes it does work off Sundays and may not be precise for the kind of calculation I want.

 

[khwaja]

What is the best way to calculate a week based on Monday - Friday based week?

 

[MarkK]

Check out a technical reference for the DateDiff function. It has an optional parameter called FirstDayOfWeek, so you can choose which day it counts. Sunday is just the default.

 

[khwaja]

Thanks. I think you are referring to this:

DateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear])

This is great help but how do I treat the week as 5 days starting from Monday and ending Friday?

 

[plog]

You haven't really explained your situation or ultimate objective. Most likely you are going to have to create your own function.

how do I treat the week as 5 days starting from Monday and ending Friday?

Its your business rules, you need to tell us how you want to handle it. My advice is to provide some sample data. Give us a scenario and then tell us what you expect as a result.

For example,

A customer orders a product on 6/9/2015 and it is picked up by the customer on 6/18/2015, I want it to calculate 2 weeks for the hold time.

 

[MarkK]

This is great help but how do I treat the week as 5 days starting from Monday and ending Friday?

You can't. For the purposes of DateDiff(), and for reality in general, a week is seven day circle. You can pick where that circle starts, but it will always be seven days. If you want to guarantee that the Monday to Friday sequence is always treated as a single lump, then select Saturday or Sunday as your start date.

 

[jdraw]

I created a small sub to see/do a few calculations.

'---------------------------------------------------------------------------------------
' Procedure : testweeks
' Author    : mellon
' Date      : 09/06/2015
' Purpose   : This routine takes 2 dates and determines the number of days between them.
'It also displays the number of weeks between them based on dividing the days between by 7(days per week)'
'It also uses the Access Datediff to get the number of "w"eeks between the dates.
'And it also uses datePart to get the week number of each date and shows the difference in week numbers
'to identify the number of weeks between the dates.
'---------------------------------------------------------------------------------------
'
Sub testweeks()
          Dim date1 As Date
          Dim date2 As Date
10        date1 = #4/20/2015#    'this is a Monday
20        date2 = #9/25/2015#    'this is a Friday
30        Debug.Print " Date1 is " & date1
40        Debug.Print " Date2 is " & date2
50        Debug.Print "Number of days between dates  " & DateDiff("d", date1, date2)
60        Debug.Print "(Number of days between dates / 7 days per week) " & DateDiff("d", date1, date2) / 7
70        Debug.Print "Weekday of Date1  " & WeekdayName(Weekday(date1))
80        Debug.Print "Weekday of date2  " & WeekdayName(Weekday(date2))
90        Debug.Print "Number of weeks between date1 and date2  " & DateDiff("w", date1, date2)
100       Debug.Print "Date1 is in week " & DatePart("ww", date1)
110       Debug.Print "Date2 is in week " & DatePart("ww", date2)
120       Debug.Print "Number of weeks between Date1 and date2 using Access's week number  " & DatePart("ww", date2) - DatePart("ww", date1)

End Sub

Results (note I'm using Canadian regional setting my dates display d/m/y)

  Date1 is 20/04/2015
 Date2 is 25/09/2015
Number of days between dates  158
(Number of days between dates / 7 days per week) 22.5714285714286
Weekday of Date1  Monday
Weekday of date2  Friday
Number of weeks between date1 and date2  22
Date1 is in week 17
Date2 is in week 39
Number of weeks between Date1 and date2 using Access's week number  22

I hope this is useful.

@khwaja
I'm not sure what your concern is with a 5 day week vs a 7 day week. Perhaps you could expand on that.

 

[khwaja]

Thank you so much to all for kindly responding and contributing. We have a construction team who are required to deliver projects within 8 weeks but obviously things are different with some variation occurring to this norm. So we have a start date and a finish date and I try to calculate the progress. Using the Datediff function, I have been unwittingly calculating incorrectly for the period as some may start on any working day and finish on a different working day. Based on the responses posted here, I need to be able to consider anything started during a week (even if it is on a Friday) to be the week 1 and by the same token week with the finishing date should be the last week.

Jdraw, would I be able to use the code to get week number calculation along these lines?

 

[jdraw]

My example was just to show how some of the calculations were the same, regardless of how they were calculated.

For your situation, because your start dates can be any one of 5 (if I understand correctly), I would think it better if you were to determine project due date based on days, not weeks.
You are working with calendar weeks which have 7 days. So in my view, your example starting on a Friday with an 8 week delivery would be Friday + (8weeks*7 days/per week) or 56 days.

My DatePart example with "ww" as the interval is an Access function to determine which week number any given Date is in. So if Date1 is in week 17 and Date2 is in week 39
The difference is 22 weeks.

I think, if you have a Start Date and a projected duration in weeks, you could determine the Projected End Date (Friday's date + 56 days) and work with that.

It also seems you have definition of "week" that is not/may not be consistent across your projects.

 

[MarkK]

If I was building a progress meter, or showing elapsed weeks until or since a certain date, or something like that, I would not use DateDiff(), because I would want fractions--or decimals. If 10 days had elapsed, I would want to display +/- 1.5 weeks. And to do that is easy. Find the difference in days, and divide by 7 . . .

function ElapsedWeeks(d1 as date, d2 as date) as single
   ElapsedWeeks = Round(Abs(d1 - d2)/7, 1)
end function