This is probably an easy question, but i'll ask anyhow:

One of my subroutines is passed the current date in "mm-dd-yy"
format. For example, it would pass in a value of "12-04-09"
for today's date.

I now need some code that will give me the Sunday to Saturday
date range that coincides with the current date. Therefore,
the code should give me a start date of "11-29-09" and end
date of "12-05-09" because "12-04-09" is part of that week.

thanx again

Hello Robert

you can use the function "Weekday"

Dim MyDate, MyWeekDay
MyDate = #12 februari 1969#
MyWeekDay = Weekday(MyDate)





"Robert Crandal" wrote:

> This is probably an easy question, but i'll ask anyhow:
>
> One of my subroutines is passed the current date in "mm-dd-yy"
> format. For example, it would pass in a value of "12-04-09"
> for today's date.
>
> I now need some code that will give me the Sunday to Saturday
> date range that coincides with the current date. Therefore,
> the code should give me a start date of "11-29-09" and end
> date of "12-05-09" because "12-04-09" is part of that week.
>
> thanx again
>
> .
>

Hello Robert,

you can use the function "Weekday"

Dim MyDate, MyWeekDay, Day1, Day2, Day3, Day4, Day5, Day6, Day7
MyDate = #12 februari 1969#
MyWeekDay = Weekday(MyDate)

Select case MyWeekDay
case 1
Day1 = MyDate
Day2 = MyDate + 1
Day3 = MyDate + 2
(and so on)
case 2
Day1 = MyDate - 1
Day2 = MyDate
Day3 = MyDate + 1
....
case 3
Day1 = MyDate - 2
....
case 4
....
end Select

"Robert Crandal" wrote:

> This is probably an easy question, but i'll ask anyhow:
>
> One of my subroutines is passed the current date in "mm-dd-yy"
> format. For example, it would pass in a value of "12-04-09"
> for today's date.
>
> I now need some code that will give me the Sunday to Saturday
> date range that coincides with the current date. Therefore,
> the code should give me a start date of "11-29-09" and end
> date of "12-05-09" because "12-04-09" is part of that week.
>
> thanx again
>
> .
>

On Fri, 4 Dec 2009 03:58:22 -0700, "Robert Crandal" <(E-Mail Removed)> wrote:

>This is probably an easy question, but i'll ask anyhow:
>
>One of my subroutines is passed the current date in "mm-dd-yy"
>format. For example, it would pass in a value of "12-04-09"
>for today's date.
>
>I now need some code that will give me the Sunday to Saturday
>date range that coincides with the current date. Therefore,
>the code should give me a start date of "11-29-09" and end
>date of "12-05-09" because "12-04-09" is part of that week.
>
>thanx again

Here's one way to get those two dates:

================================
Option Explicit
Sub WeekRange()
Dim dt As Date
dt = #12/4/2009#
Debug.Print WeekDates(dt)(0), WeekDates(dt)(1)
End Sub
'------------------------------------------------
Function WeekDates(dt As Date) As Variant
Dim StartDt As Date, EndDt As Date
Dim sTemp(0 To 1)

StartDt = dt - Weekday(dt, vbMonday)
EndDt = dt + 7 - Weekday(dt, vbSunday)

sTemp(0) = StartDt
sTemp(1) = EndDt

WeekDates = sTemp
End Function
====================================
--ron

[venkat1926]

if you are satisfied with formula instead of a code
date is in A1

=A1-WEEKDAY(A1)+1
will give you beginning of the week

=A1+7-WEEKDAY(A1)
will give you the last day of the week (week is from Sunday to Saturday)

try some experiments

A little more concise ...

Dim MyDate, MyWeekDay, Day1, Day2, Day3, Day4, Day5, Day6, Day7
MyDate = #12 februari 1969#
MyWeekDay = Weekday(MyDate)

Day1= MyDate - Choose(Weekday(MyDate),0,1,2,3,4,5,6)
Day2 = Day1 + 1
Day3 = Day1 + 2
Day4 = Day1 + 3

etc.

--
Learning something new & wonderful every day ...


"Robert Crandal" wrote:

> This is probably an easy question, but i'll ask anyhow:
>
> One of my subroutines is passed the current date in "mm-dd-yy"
> format. For example, it would pass in a value of "12-04-09"
> for today's date.
>
> I now need some code that will give me the Sunday to Saturday
> date range that coincides with the current date. Therefore,
> the code should give me a start date of "11-29-09" and end
> date of "12-05-09" because "12-04-09" is part of that week.
>
> thanx again
>
> .
>

Thank you everyone who responded to this thread.
You are all awesome!!!!