How to calculate what week it is from the current date

[sjg0003]

I have a couple forms in which I have revolving work-in/work-out. Rather than having to type in what week it is I would like to be able to update my revolving wrk-in/wrk-out DB by pressing a button.

It would just add the current date into the date field and calculate what week it is and populate the week field.

Any suggestions?

table wrk-in
id
date-in
week

table wrk-out
id
date-out
week

 

[BobJacksonNCI]

Hi,

Could you be more specific?

Are you looking for a date range, week number, date ending...

To get you started:

Dec 04, 2002 - Microsoft Knowledge Base Article – 210604
ACC2000: Functions for Calculating and Displaying Date/Time Values
Displaying Specific Dates
To display specific dates, you can use the DateSerial() function to manipulate the day, month, and year portions of a date. For example, you can use the following expressions in the ControlSource property of a text box or in the Immediate window to return specific dates:

· The current month:
DateSerial(Year(Date()), Month(Date()), 1)

· The next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)

· The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)

· The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)

· The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)

· The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)

· The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

· The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

· The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1

· The last day of the current week:
Date() - WeekDay(Date()) + 7

· The first day of the current week (using settings in Options dialog box):
Date() - WeekDay(Date(), 0) + 1

· The last day of the current week:
Date() - WeekDay(Date(), 0) + 7



HTH,
Bob

 

[sjg0003]

What I need to do is convert the current day

example:
01/06/2004 = Week 2
01/15/2004 = Week 3

January 6th falls under week 2 and January 15th falls under week 3.

 

[RoyVidar]

Try the datepart function:

[tt]datepart("ww",dtyourdate)[/tt]

Look it up in help. There are two optional arguements, firstdayofweek and firstdayofyear, that you might take a look at if your settings differ from the US, to get the correct week number.

Roy-Vidar

 

[maudedo]

i sometimes use the format$ function. ie format$(Date,"ww/yy") (or in a query would be format$(Date(),"ww/yy")) this will return the calendar week on which the date resides.