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

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/

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

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/

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

Week Ending Formula
 

Jako

This will give next Saturday's date

dDate = DateValue(Now()) + (7 - WeekDay(Now())

--
Message posted from http://www.ExcelForum.com