Excel - Employee Timesheet Manipulation

Attaching the sample input file

• If we have fixed week offs ( Sat & Sunday) and holiday calendar

  • We can consider Saturday and Sunday as default WO ( if employee not filled )

If we have holidays on particular month (Consider 1 and 15 dec as general holiday) , holidays should be displayed as H if employee not filled on particular days

If we get zero hours ( excluding on WO / holidays) then it will be consider as PL

If we get worked hours less than 4 hours it will be consider as PL , 4 to 6 hours- half day(HD) , if employee worked more than 6 hours it should be full day ( in working days only)

If we get entire week or last week zero hours then it should be highlighted ( it means employee not submitted / Approvals pending/Absconded)

test.xlsx (12.3 KB)

1 Like

Hey @shirin.sebatina

It’s a bit confusing on seeing the sample sheet shared when I compared it with the explanation.

Kindly could you please confirm is it the relevant file or please help me understand a bit more.

Thanks
#nK

Hi @Nithinkrishna

1.First we have to check for the weekend dates and mark them WO , but if there is data entered then nothing has to be done
2.Check if there is any public holiday in that current month and update it as H , if there is data entered then nothing has to be done
3.Check if on week days there is any blank or 0h 0m then replace it with PL.
4. Check on week days if there are no entries or 0h 0m for more than one week continuously then highlight those rows

hope this is bit clear

1 Like

Hey @shirin.sebatina

Will the excel file always have the days mentioned in the second row of the table ?

Kindly confirm.

Thanks
#nK

Hi @Nithinkrishna

No, its not mentioned

1 Like

Hey @shirin.sebatina

Please find a demo which covers the first point you mentioned

ExcelEmployeeTimesheetManipulation_05Feb2022_2148.zip (11.1 KB)

You can please understand the logic & try for the other points. If you face any difficulties please let us know.

Hope this helps.

Thanks
#nK

Hi @Nithinkrishna

Thankyou So much , it works