how to solve this problem

hi there.
I have a problem to solve and hope You could help me. I have to create a macro which will count cells with date and hour enteries from 00:00 to 07:00 and 20:00 to 00:00. the enteries are in YYYY-MM-DD HH:MM:SS format. theres also the problem of saturdays and sundays where all 'actions' in those days count. I have an idea how to do it but don't know how to realize it.

d=2006-01-02 00:00:00//start date

for z=1 to 5 //to know which day of the week it is

do

d+z=b
z=z+1

countif b > 00:00 and < 07:00
and b > 20:00 and < 00:00

for z = 6 to 7
do
count all enteries
end

Please help.

the date and time enteries are in the E column. the sum, well, the W column would be just fine

with the Sat/Sun problem I ment the 48h because if it was the 00:00 to 07:00 and 20:00 to 00:00 there would bo no problem to find out which day of the week it was. I just need the sum for the business week and Sat/Sun separate. only the W and the next columns are free to use.

Thanks for helping me out with this.

well, i forgot to write, my sheets names are: GDA, POZ, POZN, WAR, KATN, but i guess that something like UserSheet = AcitveSheet should solve it.in general the macro dosen't work. After I change the "Sheet1" into "POZ" in the line For Each cell In Sheets("Sheet1").Range("D1", Range("A65536").End(xlUp).Address) and in With Sheets("POZ") i get an runtime error '13' - type mismatch in the line If Weekday(DateofEntry, vbMonday) < 6 Then.
Please excuse my ignorance in this field that may cause missunderstanding. I really appriciate Your help.

I'll send You my .xls in a moment

I've recieved your xls but when I trieid to run it, I.ve got the '13' error... but I took my time today and modified your code a little bit and came out with this:

Sub statistics()
'Column W(Description) And X(counts)
'Rows 1 - 3
'Business weekday 00:00 - 07:00 i 20:00 do 24:00 Count1
'Sat/Sun Count2
'not important Count3

Dim DateofEntry As String
Dim TimeofEntry As String
Dim conventry As String
Dim count1 As Integer
Dim count2 As Integer
Dim count3 As Integer
Dim WS As Worksheet
' Zero counts
count1 = 0
count2 = 0
count3 = 0
On Error GoTo errhandle
For Each WS In Worksheets
count1 = 0
count2 = 0
count3 = 0


For Each cell In WS.Range("E2", Range("E65536").End(xlUp).Address)
'Note: format of cell YYYY-MM-DD HH:MM:SS

If cell.Value <> "" And Len(cell.Value) > 0 Then

conventry = Format(cell.Value, "yyyy-mm-dd hh:mm:ss")

DateofEntry = Left(conventry, 19)
TimeofEntry = Replace((Right(conventry, 9)), ":", "")

If Weekday(DateofEntry, vbMonday) < 6 And TimeofEntry >= 0 And TimeofEntry <= 70000 Then count1 = count1 + 1


Else: count3 = count2 + count1

End If
Next cell


For Each cell In WS.Range("E2", Range("E65536").End(xlUp).Address)
'Note: format of cell YYYY-MM-DD HH:MM:SS

If cell.Value <> "" And Len(cell.Value) > 0 Then

conventry = Format(cell.Value, "yyyy-mm-dd hh:mm:ss")

DateofEntry = Left(conventry, 19)
TimeofEntry = Replace((Right(conventry, 9)), ":", "")

If Weekday(DateofEntry, vbMonday) < 6 And TimeofEntry >= 200000 And TimeofEntry <= 239999 Then count1 = count1 + 1

Else: count3 = count2 + count1

End If
Next cell


For Each cell In WS.Range("E2", Range("E65536").End(xlUp).Address)
'Note: format of cell YYYY-MM-DD HH:MM:SS

If cell.Value <> "" And Len(cell.Value) > 0 Then

conventry = Format(cell.Value, "yyyy-mm-dd hh:mm:ss")

DateofEntry = Left(conventry, 19)
TimeofEntry = Replace((Right(conventry, 9)), ":", "")

If Weekday(DateofEntry, vbMonday) >= 6 Then count2 = count2 + 1

Else: count3 = count2 + count1

End If

Next cell


With WS
.Range("W3").Value = "Business weekday"
.Range("W4").Value = "Sat & Sun"
.Range("X2").Value = "il"
.Range("X3").Value = count1 'Business weekday
.Range("X4").Value = count2 'Sat & Sun
End With
Next WS
Exit Sub

errhandle:
If Err.Number = 13 Then
MsgBox " Sprawdż format komórek: RRRR-MM-DD HH:MM:SS"
Err.Clear
End If


End Sub

and it works. altough I'm thinking to optimize it a little. additionally I'll have to make this macro to get data from another Sheet.

i didn't get the meaning of your first sentence

ok...this is find you just plit the if's out into two different loops

I'm not English/American and I don't understand everything. I didn't need the summary count, this code does great! Thanks for all Your help!

I had to split the if's because that was the only way the macro worked properly... I tried variuos combinations and non of them were good. I took me some time, after all I'm a complete VB novice.