Rounding Dates

[Guest]

Hi All,

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

Thanks,
Aaron

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

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)

 

[Myrna Larson]

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

 

[Jerry W. Lewis]

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