Removing Weekends From List of Dates

[mac7attack]

Hi,

Is there to remove weekends from a list of dates?
I run a phone report that tells me when phone calls were recieved. I run the report from say March 18 to July 25th.
Assuming that there will be multiple calls per day, I need to remove the calls that were placed on weekends. The cell with the date in it, will also have a time in it. (i.e. 4/18/2005 4:16:06 PM). It would be a problem to ask the user to input the starting day's day of the week.

I know this will get tricky because of the 28/30/31 days in a month. At this point, I am not concerned about removing those placed on holidays.


Thank you in advance,
MAC

 

[mintjulep]

Look at the Weekday function.

 

[macropod]

Hi MAC,

I'm assuming you're using Excel, in which case you could run a macro to hide or delete rows with weekend dates. The following macro assumes column A contains the dates:

Sub Report_Format()
Dim CurrentRow As Long
Dim UsedRows As Range
On Error GoTo Abort
Set UsedRows = ActiveSheet.UsedRange.Rows ' or = Selection.Rows for a limited range
For CurrentRow = UsedRows.Rows.Count To 1 Step -1
If Int(Application.WorksheetFunction.Weekday(UsedRows.Rows(CurrentRow).Columns("A:A"))) Mod 7 < 2 Then
' Use the following to delete the offending row:
'UsedRows.Rows(CurrentRow).EntireRow.Delete
' Use the following to hide the offending row:
'UsedRows.Rows(CurrentRow).EntireRow.Hidden = True
' Use the following line to unhide rows if there is a risk the data have changed
'Else: UsedRows.Rows(CurrentRow).EntireRow.Hidden = False
End If
Next CurrentRow
' If only hiding for printing purposes, use the next two lines to print or preview then restore the worksheet
'ActiveWindow.SelectedSheets.PrintPreview
'ActiveSheet.Rows.EntireRow.Hidden = False
Abort:
End Sub

Cheers

 

[mac7attack]

Thanks macropod

What is the syntax/variables for Weekday()?

 

[macropod]

Hi MAC,

Weekday() is a standard worksheet function, not a vba function per se. Full details of the syntax etc are available from the Excel Help file.

Cheers

 

[mintjulep]

Weekday() also works in VBA.

Weekday(date) returns an integer of 1 to 7 (or zero to 6?), corresponding to Sunday, Monday .....