Calculate week from current day
in [Programming]
|
Prev: Email excel - 1 sheet in body 1 attachment
Next: Syntax of Commands Created by Macro Recording (VB_VarUserMemId)
From: Robert Crandal on 4 Dec 2009 05:58 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
From: toon_24 on 4 Dec 2009 06:43 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 > > . >
From: Cheetahke on 4 Dec 2009 07:06 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 > > . >
From: Ron Rosenfeld on 4 Dec 2009 07:11 On Fri, 4 Dec 2009 03:58:22 -0700, "Robert Crandal" <nobody(a)gmail.com> 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
From: Sensetech on 4 Dec 2009 07:31
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 > > . > |