Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate week from current day
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate week from current day
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate week from current day
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate week from current day
On Fri, 4 Dec 2009 03:58:22 -0700, "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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate week from current day
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate week from current day
Thank you everyone who responded to this thread.
You are all awesome!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current year and current week number | Excel Discussion (Misc queries) | |||
current week, current month, current year | Excel Programming | |||
Auto calculate day of week to week of the year (not as serial) | Excel Discussion (Misc queries) | |||
How do I enter the formula to show the current day of the week ? | Excel Worksheet Functions | |||
Show week number in current month | Excel Discussion (Misc queries) |