Leave tracker

[Jagdev Singh]

Hi Experts
I am working on creating a leave tracker which will give the below stats on each individual of the team. I need to capture the below stats in the first sheet (Yearly). I created the attached file, but need your assistance to complete it.
P Present
HD Half Day Leave
PL Planned Leave
UL Unplanned Leave
SL Sick Leave
LI/EO Late In/Early Out
Regards,
JD

 

[Deepak]

Little confusion so do the manual for a month & upload.

 

[Khalid NGO]

Hi JD,

I think you need to create separate 6 columns for Total P,Total HD, Total PL, Total UL, Total SL, Total LI/EO in each month sheet.
=COUNTIF(Table3[[#This Row],[Sun]:[Tue25]],"P")
=COUNTIF(Table3[[#This Row],[Sun]:[Tue25]],"HD")
=COUNTIF(Table3[[#This Row],[Sun]:[Tue25]],"PL")
and so on....

And do what sir Deepak said,
Regards,

 

[Jagdev Singh]

Hi Khalid and Deepak

I somehow manage to calculate the stats for the month of Jan and it is attached with the mail. I am getting stuck to calculate half day. Is it possible to calculate 1 Half day as 0.5 instead of 1.

Regards,
JD

 

[Deepak]

Hi Khalid and Deepak

I somehow manage to calculate the stats for the month of Jan and it is attached with the mail. I am getting stuck to calculate half day. Is it possible to calculate 1 Half day as 0.5 instead of 1.

Regards,
JD

Is this...
=COUNTIF(Table1[@[Thu]:[Sat25]],"HL")*0.5

 

[Deepak]

hi JD,

you might looking for answers in C24 {on first attachment} in yearly sheet then make sure all sheet data in same order.


C24=SUM(Jan:Dec!AI4)

or adjust as per sheet/type

 

[Jagdev Singh]

Hi Deepak

Thanks for the above formula. It really saved my team to add the formula for 12 sheets.

Regards,
JD

 

[Deepak]

Welcome jagga! Glad it worked.