Need VBA to calc week-ending date 
Author Message
 Need VBA to calc week-ending date

It's difficult to know how to begin asking this question.

I created a weekly status report template in Word 97 that
will need to contain some automated features.
One of these features is a pre-filled "week ending" text
field with the current week's Friday date (but the user
could type a different date if reporting for a different
week).

The code should run when the file is opened, only if no
date already exists in the field, to avoid over-writing
the date if the report had been opened at least once
already (unless the VBA can be tied to the .dot template
only, so it would not run when the .doc file opens)?

With no practical experience in VBA, I wonder if someone
might be willing to help, or to share code that
accomplishes something similar.

I wondered if it's possible to use the text version of the
current day (for example, Monday) to determine how many
days to add to the current date, to come up with the
Friday date. Is the text day available on a Windows
system? Is there a better method?

What field(s) contain the system date, for the
calculation? Is a format mask necessary to output the date
in a specific numeric format (mm/dd/20yy for example)?

I would be very grateful for any insights you could
provide. Thanks for taking the time to read through this!



Sat, 30 Jul 2005 03:05:19 GMT  
 Need VBA to calc week-ending date
Hi Suzanne,

Here's a function that does what you want.
Takes a date as argument, and returns the friday date in the same week.

Expl:
The function adds a day to current as long as the week is
the same. I'm having the weeknumber increment by friday.

Function GetFridayDate(ByVal CurrentDate As Date) As Date
    Dim iDay    As Integer
    Dim iWeek   As Integer
    iDay = 1
    iWeek = DatePart("ww", CurrentDate, vbFriday)
    Do Until iWeek < DatePart("ww", _
        DateAdd("d", iDay, CurrentDate), vbFriday)
        iDay = iDay + 1
    Loop
    GetFridayDate = DateAdd("d", iDay, CurrentDate)
End Function

You can check the output of the function in the immediate window
testing results listed below.

? GetFridayDate(dateserial(2003, 2, 10))
14-2-2003
? GetFridayDate(dateserial(2003, 2, 11))
14-2-2003
? GetFridayDate(dateserial(2003, 8, 13))
15-8-2003

Utilizing it in yr situation:
You could place a FormField in the document named: EndOfWeekDate
In the Document_New event of the template go:

ActiveDocument.FormFields(1).TextInput.Default = _
    GetFridayDate(Now)

Krgrds,
Perry



Quote:
> It's difficult to know how to begin asking this question.

> I created a weekly status report template in Word 97 that
> will need to contain some automated features.
> One of these features is a pre-filled "week ending" text
> field with the current week's Friday date (but the user
> could type a different date if reporting for a different
> week).

> The code should run when the file is opened, only if no
> date already exists in the field, to avoid over-writing
> the date if the report had been opened at least once
> already (unless the VBA can be tied to the .dot template
> only, so it would not run when the .doc file opens)?

> With no practical experience in VBA, I wonder if someone
> might be willing to help, or to share code that
> accomplishes something similar.

> I wondered if it's possible to use the text version of the
> current day (for example, Monday) to determine how many
> days to add to the current date, to come up with the
> Friday date. Is the text day available on a Windows
> system? Is there a better method?

> What field(s) contain the system date, for the
> calculation? Is a format mask necessary to output the date
> in a specific numeric format (mm/dd/20yy for example)?

> I would be very grateful for any insights you could
> provide. Thanks for taking the time to read through this!



Sat, 30 Jul 2005 04:22:23 GMT  
 Need VBA to calc week-ending date
Perry, thank you!
I'll be sure to let you know how it comes out.

Quote:
>-----Original Message-----
>Hi Suzanne,

>Here's a function that does what you want.
>Takes a date as argument, and returns the friday date in
the same week.

>Expl:
>The function adds a day to current as long as the week is
>the same. I'm having the weeknumber increment by friday.

>Function GetFridayDate(ByVal CurrentDate As Date) As Date
>    Dim iDay    As Integer
>    Dim iWeek   As Integer
>    iDay = 1
>    iWeek = DatePart("ww", CurrentDate, vbFriday)
>    Do Until iWeek < DatePart("ww", _
>        DateAdd("d", iDay, CurrentDate), vbFriday)
>        iDay = iDay + 1
>    Loop
>    GetFridayDate = DateAdd("d", iDay, CurrentDate)
>End Function

>You can check the output of the function in the immediate
window
>testing results listed below.

>? GetFridayDate(dateserial(2003, 2, 10))
>14-2-2003
>? GetFridayDate(dateserial(2003, 2, 11))
>14-2-2003
>? GetFridayDate(dateserial(2003, 8, 13))
>15-8-2003

>Utilizing it in yr situation:
>You could place a FormField in the document named:
EndOfWeekDate
>In the Document_New event of the template go:

>ActiveDocument.FormFields(1).TextInput.Default = _
>    GetFridayDate(Now)

>Krgrds,
>Perry


in bericht

>> It's difficult to know how to begin asking this
question.

>> I created a weekly status report template in Word 97
that
>> will need to contain some automated features.
>> One of these features is a pre-filled "week ending" text
>> field with the current week's Friday date (but the user
>> could type a different date if reporting for a different
>> week).

>> The code should run when the file is opened, only if no
>> date already exists in the field, to avoid over-writing
>> the date if the report had been opened at least once
>> already (unless the VBA can be tied to the .dot template
>> only, so it would not run when the .doc file opens)?

>> With no practical experience in VBA, I wonder if someone
>> might be willing to help, or to share code that
>> accomplishes something similar.

>> I wondered if it's possible to use the text version of
the
>> current day (for example, Monday) to determine how many
>> days to add to the current date, to come up with the
>> Friday date. Is the text day available on a Windows
>> system? Is there a better method?

>> What field(s) contain the system date, for the
>> calculation? Is a format mask necessary to output the
date
>> in a specific numeric format (mm/dd/20yy for example)?

>> I would be very grateful for any insights you could
>> provide. Thanks for taking the time to read through
this!

>.



Sat, 30 Jul 2005 06:23:19 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Need day of week calc please

2. Beginning and ending dates of a specified week

3. Calculate week ending date .......

4. javascript to fill combo box with weekending dates automatically needed

5. VBA Code to Insert Path/Filename/Date/Time at end of document

6. Date / Same date or first day of week

7. date calc problem

8. week ending

9. Calc Date

10. Multiple Dates by choosing Start and End Date

11. Finding the date(s) datapoints between a given start and end date, while excluding weekends

12. start date/time, end date/time problem

 

 
Powered by phpBB® Forum Software