# generating dates on a report

G

#### Guest

I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday. Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)

J

#### John Spencer

Week Ending Date: Use the formula

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

G

#### Guest

Thanks John. It works but I don't really understand the formula. Can you

John Spencer said:
Week Ending Date: Use the formula

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

Bobbye R said:
I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are
the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the
pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday.
Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)

J

#### John Spencer

The formula is using the VBA function Choose, DateAdd, and Weekday. If you open
a module window, you can type in the formula and highlight each Function name to

Quick synopis:

Weekday returns a number corresponding to the day of the week for a date. I used
the optional argument ",2" to tell the function that Monday was the first day of
the week. So it returns a value between 1 and 7

Choose takes a number argument in the first position and selects the
corresponding value in the following list of values.

Don't have any specific recommendation on books for learning VBA.

Bobbye said:
Thanks John. It works but I don't really understand the formula. Can you

John Spencer said:
Week Ending Date: Use the formula

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

Bobbye R said:
I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are
the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the
pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday.
Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)

G

#### Guest

Thanks again for taking the time.
--
Ameteur Access 2000 User
Thanks Bobbye

John Spencer said:
The formula is using the VBA function Choose, DateAdd, and Weekday. If you open
a module window, you can type in the formula and highlight each Function name to

Quick synopis:

Weekday returns a number corresponding to the day of the week for a date. I used
the optional argument ",2" to tell the function that Monday was the first day of
the week. So it returns a value between 1 and 7

Choose takes a number argument in the first position and selects the
corresponding value in the following list of values.

Don't have any specific recommendation on books for learning VBA.

Bobbye said:
Thanks John. It works but I don't really understand the formula. Can you

John Spencer said:
Week Ending Date: Use the formula

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are
the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the
pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday.
Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)