473,738 Members | 10,068 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Week calculations (Date/Time)

Hello,

I'm looking for a function that returns a date range for a specified week
number of the year.

I'm not able to find functions like this anywhere. Any pointers/help would
be much appreciated.

TIA

Jim
Jul 19 '05 #1
2 5217
hi,
try these:

Microsoft® Visual Basic® Scripting Edition
DateDiff Function Language Reference
Version 2
See Also
----------------------------------------------------------------------------
----

Description
Returns the number of intervals between two dates.
Syntax
DateDiff(interv al, date1, date2 [,firstdayofweek[, firstweekofyear]])
The DateDiff function syntax has these parts:

Part Description
interval Required. String expression that is the interval you want
to use to calculate the differences between date1 and date2. See Settings
section for values.
date1, date2 Required. Date expressions. Two dates you want to use
in the calculation.
firstdayofweek Optional. Constant that specifies the day of the
week. If not specified, Sunday is assumed. See Settings section for values.
firstweekofyear Optional. Constant that specifies the first week of
the year. If not specified, the first week is assumed to be the week in
which January 1 occurs. See Settings section for values.

Settings
The interval argument can have the following values:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week of year
h Hour
n Minute
s Second
The firstdayofweek argument can have the following values:

Constant Value Description
vbUseSystem 0 Use National Language Support (NLS) API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
The firstweekofyear argument can have the following values:

Constant Value Description
vbUseSystem 0 Use National Language Support (NLS) API setting.
vbFirstJan1 1 Start with the week in which January 1 occurs
(default).
vbFirstFourDays 2 Start with the week that has at least four days in
the new year.
vbFirstFullWeek 3 Start with the first full weekof the new year.
Remarks
You can use the DateDiff function to determine how many specified time
intervals exist between two dates. For example, you might use DateDiff to
calculate the number of days between two dates, or the number of weeks
between today and the end of the year.
To calculate the number of days between date1 and date2, you can use
either Day of year ("y") or Day ("d"). When interval is Weekday ("w"),
DateDiff returns the number of weeks between the two dates. If date1 falls
on a Monday, DateDiff counts the number of Mondays until date2. It counts
date2 but not date1. If interval is Week ("ww"), however, the DateDiff
function returns the number of calendar weeks between the two dates. It
counts the number of Sundays between date1 and date2. DateDiff counts date2
if it falls on a Sunday; but it doesn't count date1, even if it does fall on
a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function
returns a negative number.

The firstdayofweek argument affects calculations that use the "w" and "ww"
interval symbols.

If date1 or date2 is a date literal, the specified year becomes a
permanent part of that date. However, if date1 or date2 is enclosed in
quotation marks (" ") and you omit the year, the current year is inserted in
your code each time the date1 or date2 expression is evaluated. This makes
it possible to write code that can be used in different years.

When comparing December 31 to January 1 of the immediately succeeding
year, DateDiff for Year ("yyyy") returns 1 even though only a day has
elapsed.

The following example uses the DateDiff function to display the number of
days between a given date and today:

Function DiffADate(theDa te)
DiffADate = "Days from today: " & DateDiff("d", Now, theDate)
End Function
DatePart Function Language Reference Version 2 See
Also -----------------------------------------------------------------------
-----DescriptionRetu rns the specified part of a given
date.SyntaxDate Part(interval, date[, firstdayofweek[, firstweekofyear]]) The
DatePart function syntax has these parts: Part Description interval
Required. String expression that is the interval of time you want to return.
See Settings section for values. date Required. Date expression you want to
evaluate. firstdayof week Optional. Constant that specifies the day of the
week. If not specified, Sunday is assumed. See Settings section for values.
firstweekofyear Optional. Constant that specifies the first week of the
year. If not specified, the first week is assumed to be the week in which
January 1 occurs. See Settings section for values. SettingsThe interval
argument can have the following values: Setting Description yyyy Year q
Quarter m Month y Day of year d Day w Weekday ww Week of year h Hour n
Minute s Second The firstdayofweek argument can have the following values:
Constant Value Description vbUseSystem 0 Use National Language Support (NLS)
API setting. vbSunday 1 Sunday (default) vbMonday 2 Monday vbTuesday 3
Tuesday vbWednesday 4 Wednesday vbThursday 5 Thursday vbFriday 6 Friday
vbSaturday 7 Saturday The firstweekofyear argument can have the following
values: Constant Value Description vbUseSystem 0 Use National Language
Support (NLS) API setting. vbFirstJan1 1 Start with the week in which
January 1 occurs (default). vbFirstFourDays 2 Start with the week that has
at least four days in the new year. vbFirstFullWeek 3 Start with the first
full weekof the new year. RemarksYou can use the DatePart function to
evaluate a date and return a specific interval of time. For example, you
might use DatePart to calculate the day of the week or the current hour. The
firstdayofweek argument affects calculations that use the "w" and "ww"
interval symbols. If date is a date literal, the specified year becomes a
permanent part of that date. However, if date is enclosed in quotation marks
(" "), and you omit the year, the current year is inserted in your code
each time the date expression is evaluated. This makes it possible to write
code that can be used in different years. This example takes a date and,
using the DatePart function, displays the quarter of the year in which it
occurs. Function GetQuarter(TheD ate)
GetQuarter = DatePart("q", TheDate)
End Function
----------------------------------------------------------------------------
DateSerial Function Language Reference Version 1 See
Also -----------------------------------------------------------------------
-----DescriptionRetu rns a Variant of subtype Date for a specified year,
month, and day.SyntaxDateS erial(year, month, day) The DateSerial function
syntax has these arguments: Part Description year Number between 100 and
9999, inclusive, or a numeric expression. month Any numeric expression. day
Any numeric expression. RemarksTo specify a date, such as December 31, 1991,
the range of numbers for each DateSerial argument should be in the accepted
range for the unit; that is, 1-31 for days and 1-12 for months. However, you
can also specify relative dates for each argument using any numeric
expression that represents some number of days, months, or years before or
after a certain date. The following example uses numeric expressions instead
of absolute date numbers. Here the DateSerial function returns a date that
is the day before the first day (1 - 1) of two months before August (8 - 2)
of 10 years before 1990 (1990 - 10); in other words, May 31, 1980. Dim
MyDate1, MyDate2
MyDate1 = DateSerial(1970 , 1, 1) ' Returns January 1, 1970.
MyDate2 = DateSerial(1990 - 10, 8 - 2, 1 - 1) ' Returns May 31, 1980.
For the year argument, values between 0 and 99, inclusive, are interpreted
as the years 1900-1999. For all other year arguments, use a complete
four-digit year (for example, 1800). When any argument exceeds the accepted
range for that argument, it increments to the next larger unit as
appropriate. For example, if you specify 35 days, it is evaluated as one
month and some number of days, depending on where in the year it is applied.
However, if any single argument is outside the range -32,768 to 32,767, or
if the date specified by the three arguments, either directly or by
expression, falls outside the acceptable range of dates, an error
occurs.DateValu e Function Language Reference Version 1 See
Also -----------------------------------------------------------------------
---DescriptionRetu rns a Variant of subtype Date.SyntaxDate Value(date) The
date argument is normally a string expression representing a date from
January 1, 100 through December 31, 9999. However, date can also be any
expression that can represent a date, a time, or both a date and time, in
that range.RemarksIf the date argument includes time information, DateValue
doesn't return it. However, if date includes invalid time information (such
as "89:98"), an error occurs. If date is a string that includes only numbers
separated by valid date separators, DateValue recognizes the order for
month, day, and year according to the short date format you specified for
your system. DateValue also recognizes unambiguous dates that contain month
names, either in long or abbreviated form. For example, in addition to
recognizing 12/30/1991 and 12/30/91, DateValue also recognizes December 30,
1991 and Dec 30, 1991. If the year part of date is omitted, DateValue uses
the current year from your computer's system date. The following example
uses the DateValue function to convert a string to a date. You can also use
date literals to directly assign a date to a Variant variable, for example,
MyDate = #9/11/63#. Dim MyDate
MyDate = DateValue("Sept ember 11, 1963") ' Return a date.
--------------------------------------------------------------------------I
hope these help you!bye-- ««««««««»»»»»»» »»»»»»»Vlmar Brazão de
OliveiraDesenvo lvimento WebHI-TEC"androtech" <re********@thi stoo.com>
escreveu na mensagem news:pa******** *************** *****@thistoo.c om...
Hello,

I'm looking for a function that returns a date range for a specified week
number of the year.

I'm not able to find functions like this anywhere. Any pointers/help would
be much appreciated.

TIA

Jim

Jul 19 '05 #2
function GetweekDates(in tYear,intWeek)
GetweekDates = "Error"
intYear = Trim(intYear)
intWeek = Trim(intWeek)
If IsNUmeric(intYe ar) And IsNumeric(intWe ek) Then
If Len(intYear) = 4 And CDbl(intWeek) >= 0 And CDbl(intWeek) <= 52 Then
Dim ar(6)
dtmSetDate = DateAdd("ww",in tWeek,DateSeria l(intYear,1,1))
dtmSetDate = DateAdd("d",-(DatePart("w",d tmSetDate) - 1),dtmSetDate)
For i = 0 To 6
ar(i) = DateAdd("d",i,d tmSetDate)
Next
GetweekDates = Join(ar,",")
End If
End If
end function

-dlbjr

Discerning resolutions for the alms
Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
4674
by: lduperval | last post by:
Hi, I`m trying to do date calculations in three types of time zones: local, GMT and specified. The issue I am facing is that I need to be able to specify a date in the proper time zone, and I`m having a heck of a time doing so. I have created a form where I use drop downs do specify year, month, date, hour, minute and seconds. When the form is loaded, the dropdowns have to display the proper values for the current time zone type. This
14
5108
by: deko | last post by:
This runs, but does not narrow to current week. suggestions appreciated! SELECT lngEid, dtmApptDate, Subject, Appt_ID FROM qry002 WHERE (dtmApptDate BETWEEN DateAdd("d",-weekday()+2,) And DateAdd("d", 6, DateAdd("d", -weekday()+2,)))
4
2561
by: Mark Reed | last post by:
Hi all, I am using office XP and have a question regarding the format function. I am using wk:format(.,"WW") to show what week a certain date is. The problem is that a week runs from Sun to Sat in Access and our warehouse week runs from Sat to Fri. This is making my "week to date" calculations a day out. Is there a way that I can specify which day a new week starts on within this query???? Many thanks in advance,
5
3037
by: Peter Bailey | last post by:
I have a query that returns , and : 12/05/04 3 Wednesday 13/05/04 0 Thursday and so on what I would like to do now is count the number of bookings by week so from monday to friday from the first date get the sum of bookings.
7
2117
by: peter | last post by:
I have created a MS Access query where one of the fields displays the week number. The field is a date value that uses the ww format to display the week number. However, when I try to create a calculation such as *2 I get an incorrect value. For example, if the week number is 11 and I multiply it by 2 I get an answer of 21. How do I convert the displayed week number into an integer value so that I can then perform a calculation on it?
14
3495
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity. However, the expectation changes - it may be 60% for a while, then change to 50%. Initially, I was averaging the expectation, along with the productivity, but what I'm being asked is to look at the average productivity/performance compared to...
6
25366
by: aarklon | last post by:
Hi folks, I found an algorithm for calculating the day of the week here:- http://www.faqs.org/faqs/calendars/faq/part1/index.html in the section titled 2.5 what day of the week was 2 august 1953 I checked the algorithm it is very correct.
3
2623
by: sbaird | last post by:
Aloha from Hawaii, I'm beating my head on the wall here. I have a recruiting contact managment database I'm trying to create. Managers (there ar 14 of them) have to make a certain number of recruiting calls per week, and so many of them have to result in at least an appointment. However, the "week" is Wednesday, 12:01pm to the following Wednesday, 12:00noon. So here are my questions... 1) Is there a way to change the standard week...
9
2628
Catalyst159
by: Catalyst159 | last post by:
I have a form which is used to calculate residential Floor Area Ratio (FAR). The form is structured into seven parts as follows: Part A: Maximum FAR and Floor Area: Part B: Gross Floor Area of the main floors of the main house: Part C: Gross Floor Area of the basement or cellar: Part D: Gross Floor Area of the attic:
0
8969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9476
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9335
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9263
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9208
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8210
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6053
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4570
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2745
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.