Calculate week from current day

[Robert Crandal]

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

 

[toon_24]

Hello Robert

you can use the function "Weekday"

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

 

[Cheetahke]

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

 

[Ron Rosenfeld]

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

 

[Sensetech]

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.

 

[Robert Crandal]

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