Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding Dates
Hi All,
I am looking for a way to Round dates in a column to the previous Firday? Any Ideas? Thanks, Aaron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding Dates to Weekending Date | Excel Discussion (Misc queries) | |||
rounding dates | Excel Worksheet Functions | |||
Rounding UP Months' Difference between 2 dates | Excel Discussion (Misc queries) | |||
Rounding Dates to the 1st? | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) |