PDA

View Full Version : Montly, Weekly, Daily Attendance



Svmaxcel
12-16-2017, 03:13 AM
I am attaching a file to track absenteeism.
Column has dates, week and months.
I want to create a button to select the month, once I select the month the column should hide everything excel the month which I selected, same foes for week as well

SamT
12-16-2017, 07:17 AM
IMO, this is easier

Svmaxcel
12-17-2017, 07:49 AM
Thanks the the file, however this didn't solve my purpose.
Some emp have leave request in Jan, some have request for June, it becomes difficult to scroll through column to find the month, date.
I do not wish to group the month.
What I wanted is, if I select the month of August, the calendar will display only August and hide others.
I thought of using column hidden based on the months and had created a column reference sheet.
Pleas suggest

Logit
12-17-2017, 10:03 AM
.
Here is a method of hiding columns using a SELECT CASE function :



Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Target.Parent.Range("B2")
If Intersect(Target, rng) Is Nothing Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Cells.EntireColumn.Hidden = False
Select Case rng
Case 0
Cells.EntireColumn.Hidden = False
Case 1
Range("C1,E1,G1,I1,K1").EntireColumn.Hidden = True
Case 2
Range("D1,F1,H1,J1,L1").EntireColumn.Hidden = True
End Select


Set rng = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

snb
12-17-2017, 10:15 AM
@logit


Cells.EntireColumn.Hidden = False
Select Case rng
Case 1,2
Range("C1,E1,G1,I1,K1").offset(,rng-1).EntireColumn.Hidden = True
End Select

Svmaxcel
12-17-2017, 12:21 PM
Thanks.
Had a question.
If I make a table for absenteeism and select a month randomly, how will it calculate the data.
I mean if I select Jun, how can it check present,absent count only for June or specific week.

Logit
12-17-2017, 01:16 PM
.
https://www.extendoffice.com/documents/excel/3502-excel-exclude-hidden-columns-sum.html

Svmaxcel
12-18-2017, 08:38 AM
Please check attachment.

I used the code for hiding column but getting error in Case Oct

Questions.
1) Why am I getting error in VBA for Case Oct?

2) Suppose today is 11Feb 2018, emp came to work, so I will go to 11Feb column and add a P for present, calendar is very big, so I will have to search for the month and then date and then emp name to mark him present.
So I want is that I will select the month in A27 and all column would be hidden, except Feb, how can that be done.(same goes for week also).

3) Suppose it's 11Feb and I want to check the shrinkage for the month of Jan, I want that I should select the month in E39 and Range F36:L36 should have the required info.

4) 31st Dec is on Sunday, I used WeekNum formula on 31Dec and got the week number as 53, where in the next day its Week 1(1jan), I mean how come one week is only for 1 day.?

5) Let's go to point 2, if I select month of Feb then all other column would be hidden, but I don't want Range F36:L36 to be hidden, I guess here I can take help by using Shapes.

Please assist, I know its complicated and I m trying my best to explain all things

Svmaxcel
12-20-2017, 09:30 AM
Please also let me know, how can I use SumProduct formula to get leave count for each manager.

Logit
12-20-2017, 10:58 AM
.
Re: Question #1



Select Case rng
Case "Jan"
' Cells.EntireColumn.Hidden = False
Range("F1:AJ1").EntireColumn.Hidden = True
Case "Feb"
Range("AK1:BL1").EntireColumn.Hidden = True
Case "Mar"
Range("BM1:CQ1").EntireColumn.Hidden = True
Case "Apr"
Range("CR1:DU1").EntireColumn.Hidden = True
Case "May"
Range("DV1:EZ1").EntireColumn.Hidden = True
Case "Jun"
Range("FA1:GD1").EntireColumn.Hidden = True
Case "Jul"
Range("GE1:HI1").EntireColumn.Hidden = True
Case "Aug"
Range("HJ1:IN1").EntireColumn.Hidden = True
Case "Sep"
Range("IO1:JR1").EntireColumn.Hidden = True
Case "Oct"
Range("JS1:KW1").EntireColumn.Hidden = True
Case "Nov"
Range("KX1:MA1").EntireColumn.Hidden = True
Case "Dec"
Range("MB1:NF1").EntireColumn.Hidden = True
End Select


However, your above code HIDES the columns you are wanting to view. You will need to change your code to hide all columns EXCEPT the range you are seeking to view.

Svmaxcel
12-20-2017, 11:03 AM
Can you help me with that

Logit
12-20-2017, 02:32 PM
.


With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Cells.EntireColumn.Hidden = False
Select Case rng
Case "Jan"
Range("F1:NG1").EntireColumn.Hidden = True
Range("F1:AJ1").EntireColumn.Hidden = False
Case "Feb"
Range("AK1:BL1").EntireColumn.Hidden = True
Case "Mar"
Range("BM1:CQ1").EntireColumn.Hidden = True