Rounding Dates
 

Hi All,

I am looking for a way to Round dates in a column to the previous Firday?
Any Ideas?

Thanks,
Aaron

Rounding Dates
 

Sub Tester1()
Dim dt As Date
For Each cell In Selection
dt = cell.Value - WeekDay(cell.Value, vbFriday) + 1
cell.Value = dt
Next
End Sub

Assuming if the date is a Friday you don't want it to change.

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hi All,

I am looking for a way to Round dates in a column to the previous Firday?
Any Ideas?

Thanks,
Aaron

Rounding Dates
 

If you do, try

Sub Tester1()
Dim dt As Date
Dim cell As Range
For Each cell In Selection
If Weekday(cell.Value, vbFriday) = 1 Then
dt = cell.Value - 7
Else
dt = cell.Value - Weekday(cell.Value, vbFriday) + 1
End If
cell.Value = dt
Next
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tom Ogilvy" wrote in message
...
Sub Tester1()
Dim dt As Date
For Each cell In Selection
dt = cell.Value - WeekDay(cell.Value, vbFriday) + 1
cell.Value = dt
Next
End Sub

Assuming if the date is a Friday you don't want it to change.

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hi All,

I am looking for a way to Round dates in a column to the previous
Firday?
Any Ideas?

Thanks,
Aaron

Rounding Dates
 

The following should also work

Sub Tester1a()
Dim cell As Range
For Each cell In Selection
With cell
.Value = .Value - Weekday(.Value, vbSaturday)
End With
Next cell
End Sub

Rounding Dates
 

For a related approach, recall that a date is stored as the number of
days since the beginning of 1900. You can verify that Saturday dates
are divisible by 7. Thus
Int((dt + 1) / 7) * 7 - 1
is the nearest Friday date that is <= dt.

One caveat: Both this and the other answers will be wrong for dates
before March 1900, because Excel dates include the nonexistent date of
Feb 29, 1900 for compatibility with Lotus.

Jerry

Aaron wrote:

Hi All,

I am looking for a way to Round dates in a column to the previous Firday?
Any Ideas?