Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Ending Formula
Could someone please tell me if there is a formula to do the following.
I have a formula which puts todays day in DayTextBox. I have a formula which puts todays Date in DayTextBox. What i want to do is then put in WeekEndDayTextBox an WeekEndDateTextBox with the obvious data. The weekending day will be a friday. I want to do this on a Monday so need to check if today is Monday if it is then i want to set (o Worksheet "DUMP") cell A1 as Monday-2 (ie Saturday), cell a2 as Monday-1 (ie Sunday) etc until A7 as Monday+5 (ie Friday). All controls are on a userform (VBA). Is this possible? As mentioned i only want to allocate the data to the worksheet on Monday. TI -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Ending Formula
Dim dt as Date
if weekday(date,vbSunday) = 2 then with worksheets("dump") dt = date - 2 for i = 1 to 7 .cells(i,1).Value = dt + i - 1 .cells(i,1).Numberformat = "mm/dd/yyyy" Next End With End if -- Regards, Tom Ogilvy "Jako " wrote in message ... Could someone please tell me if there is a formula to do the following. I have a formula which puts todays day in DayTextBox. I have a formula which puts todays Date in DayTextBox. What i want to do is then put in WeekEndDayTextBox and WeekEndDateTextBox with the obvious data. The weekending day will be a friday. I want to do this on a Monday so i need to check if today is Monday if it is then i want to set (on Worksheet "DUMP") cell A1 as Monday-2 (ie Saturday), cell a2 as Monday-1 (ie Sunday) etc until A7 as Monday+5 (ie Friday). All controls are on a userform (VBA). Is this possible? As mentioned i only want to allocate the data to the worksheet on a Monday. TIA --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Ending Formula
I have tried this code Tom and nothing happens.
Also i think it would be better to have the dates instead of A1:A runniing from a2 to g2 (ie in a row). Thank -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Ending Formula
You said you only wanted it to run on Monday's.
by the date on your post: ?Format("06/22/2004","ddd") Tue It isn't Monday. If I change the code to run on Tuesday, then the dates start with Sunday since it starts with the current date minus 2 days. I adjusted the dates to go from A1 to G1 Sub Tester1() Dim dt As Date 'If WeekDay(Date, vbSunday) = 2 Then ' change 3 back to 2 With Worksheets("dump") dt = Date - Choose(WeekDay(Date, vbSunday), 1, 2, 3, 4, 5, 6, 0) For i = 1 To 7 .Cells(1, i).Value = dt + i - 1 .Cells(1, i).NumberFormat = "mm/dd/yyyy" Next End With 'End If End Sub After you test it, then If you only want it to run on Monday, then uncomment the ' If Weekday(Date . . . ' End if commands. -- Regards, Tom Ogilvy "Jako " wrote in message ... I have tried this code Tom and nothing happens. Also i think it would be better to have the dates instead of A1:A7 runniing from a2 to g2 (ie in a row). Thanks --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Ending Formula
Sorry Tom,
I actually changed the system date to a Monday and tried the cod that's when i said it didn't work. I'll try again though. Many thank -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Ending Formula
Jako
This will give next Saturday's date dDate = DateValue(Now()) + (7 - WeekDay(Now()) -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Week ending Thursday formula | Excel Worksheet Functions | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Formula for week ending | Excel Worksheet Functions | |||
Week Ending formula | Excel Discussion (Misc queries) | |||
Week ending | Excel Worksheet Functions |