Formula for calculation

Sagar SV

Hi Guys,

I'm stuck with one critical problem, where I need one formula.

I have two sheets

Sheet A (contains only applied leaves, not all the dates range)

Column 1 - Leave Start Date, eg 01 jan 24

Column 2 - Leave End date, eg 03 jan 24

Column 3 - Employee code, eg ABC001

Sheet B (contains entire range of dates for particular year)

Column 1 - Date range (i.e. 01 jan24 , 02 jan24 , 03 jan24 , etc in once column till 31st dec24)

Column 2 - on leave (or) present

Header row of column2,3, etc - employee code

I have a formula for this which was provided by Paul, where it checks from the Sheet A, whether employee is on leave or not.

=IF(COUNTIFS({Leave Register - 1 Range 1}, @cell <= Date2, {Leave Register - 1 Range 2}, @cell >= Date2, {Leave Register - 1 Range 3}, @cell = [ABC001]$1) > 0, "On Leave")

Sheet C (Contains Timesheet data of employees, which is being filled via Form)

Column 1 - Dates

Column 2 - Employee ID's

Column 3 - Hrs

Column 4 - Entry status (whenever an employee fills the form, it will show status as "Complete")

Sheet D (consolidated sheet for the timesheet entries and on leave status) - Need formula for this sheet

Column 1 - Employee ID

Column 2,3,4.. - Dates

I need a formula for the dates column where it should check the leave register, whether employee is on leave or not from sheet B, and if employee is not on leave, it should check the part whether timesheet entry is available from sheet C and should reflect the status as "on leave" or "Pending" or "complete"

At present, I'm using the below formula for the marking only "complete" or "pending"

=IF(IFERROR(INDEX(COLLECT({Timesheet Consolidated Entries Range 6}, {Timesheet Consolidated Entries Range 5}, $[Employee ID]@row), MATCH([Column2]$1, COLLECT({Timesheet Consolidated Entries Range 6}, {Timesheet Consolidated Entries Range 5}, $[Employee ID]@row), 0)), "-") = [Column2]$1, "Complete", "Pending")

Your wonderful thoughts and ideas are highly appreciated.

jmyzk_cloudsmart_jp

Hi @Sagar SV

For the Attendance Register -2 Sheet, you must change the row_index from 1 to 2, 3, 4, and so on.

The Consol Engry Sheet 4 error can happen in many ways, so please request a copy-and-share of the workspace that contains the demo solution from the link below. You will get admin access to the workspace.

Please choose "Leave and Timesheet Status Check Proces".

https://app.smartsheet.com/b/form/c06619d504f9418284b4125eec205a54 (Copy and Share Workspace Sevice Request Form)

Please refer to those column info sheets if you prefer to input the formulas yourself.

https://app.smartsheet.com/b/publish?EQBCT=e5cabd4f397b49bfa4f79c8e64e142ef

https://app.smartsheet.com/b/publish?EQBCT=8daf1bb516a9401086083a3ddadd99ea

jmyzk_cloudsmart_jp

Hi @Sagar SV

The demo solution below uses a column formula, as applying the cell formulas to many columns and rows becomes difficult and prone to easy breaks. For example, you put employee ID* and dates ** in your Sheets B and D, respectively, and reference with [column name]$1. However, I modified your original formula to reference other sheets so I could use column formulas.

(*) Employee Code

[ABC001] =COUNTIFS({Sheet A: Leave_Register : End Date}, >=Date@row, {Sheet A: Leave_Register : Start Date}, <=Date@row, {Sheet A: Leave_Register : Emp Code}, INDEX({Employee Code Index : Code}, 1) = @cell)

(**)Dates

I reference Sheet B (Attendance Register)'s dates column instead of putting them in the first row.

INDEX({Sheet B: Attendance_Register : Date}, 1) # refer to 08/01/24

INDEX({Sheet B: Attendance_Register : Date}, 2) # refer to 08/02/24

https://app.smartsheet.com/b/publish?EQBCT=8460326b6b984956803dfa828a784e76 (Sheet D: link to the published sheet)

Formulas

[Employee ID Index] =INDEX({Employee Code Index : Row ID}, MATCH([Employee ID]@row, {Employee Code Index : Code}))

[Date 1] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 1) + "", IF(COUNTIFS(INDEX({Sheet B: Attendance_Register : Leave Checked}, 0, [Employee ID Index]@row), 1, {Sheet B: Attendance_Register : Date}, INDEX({Sheet B: Attendance_Register : Date}, 1)) > 0, "On Leave", IF(CONTAINS("Complete", JOIN(COLLECT({Sheet C : Timesheet : Entry Status}, {Sheet C : Timesheet : Employee ID}, [Employee ID]@row, {Sheet C : Timesheet Range : Date}, INDEX({Sheet B: Attendance_Register : Date}, 1)))), "Complete", "Pending")))

[Date 2] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 2) + "", IF(COUNTIFS(INDEX({Sheet B: Attendance_Register : Leave Checked}, 0, [Employee ID Index]@row), 1, {Sheet B: Attendance_Register : Date}, INDEX({Sheet B: Attendance_Register : Date}, 2)) > 0, "On Leave", IF(CONTAINS("Complete", JOIN(COLLECT({Sheet C : Timesheet : Entry Status}, {Sheet C : Timesheet : Employee ID}, [Employee ID]@row, {Sheet C : Timesheet Range : Date}, INDEX({Sheet B: Attendance_Register : Date}, 2)))), "Complete", "Pending")))

Formula Breakdown: [Date 1]

1. Checking for Parent Rows:
   • IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 1) + "", ...
     • This part ensures that if the current row is a "Parent" row, the formula returns the date as a string (e.g., 01 Jan 24). Parent rows show the dates dynamically, updating dates as you change the date values in Sheet B. So you don’t want to apply further calculations here.
2. Checking if the Employee is on Leave:
   • COUNTIFS(INDEX({Sheet B: Attendance_Register : Leave Checked}, 0, [Employee ID Index]@row), 1, {Sheet B: Attendance_Register : Date}, INDEX({Sheet B: Attendance_Register : Date}, 1)) > 0
     • This COUNTIFS statement checks if the employee is marked as "On Leave" in Sheet B for the given date. If the result is greater than 0, it returns "On Leave".
     • INDEX({Sheet B: Attendance_Register : Date}, 1) refers to the specific date from Sheet B for which the check is made.
3. Checking Timesheet Completion:
   • IF(CONTAINS("Complete", JOIN(COLLECT({Sheet C : Timesheet : Entry Status}, {Sheet C : Timesheet : Employee ID}, [Employee ID]@row, {Sheet C : Timesheet Range : Date}, INDEX({Sheet B: Attendance_Register : Date}, 1)))), "Complete", "Pending")
     • If the employee is not on leave, this part checks the Timesheet Entries (Sheet C).
     • The COLLECT function retrieves the timesheet status for the matching employee and date, and JOIN combines the results. If the status is "Complete", the formula returns "Complete".
     • If the timesheet entry isn't found or incomplete, it defaults to "Pending".

In the demo sheet above, I used my API tool to populate 28 formulas for [Date 1] to [Date 28]. You can use a Google Sheet, Excel, or even a text editor to create multiple formulas by changing the row_index in the following formula part. ( INDEX({Sheet B: Attendance_Register : Date}, row_index) )

https://app.smartsheet.com/b/publish?EQBCT=b59ae84602654fbc9e1e5069ed294288 (Sheet A: link to the published sheet)

I used the following formula for the [Leave Days], as we normally count both the start and end dates of the leave of absence.

=NETWORKDAYS([Leave Start Date]@row, [Leave End Date]@row)

https://app.smartsheet.com/b/publish?EQBCT=e3e6a683ed6e48be912bb552e0aabb49 (Sheet B: link to the published sheet)

I used the flag checkbox instead of the text "On Leave" to simplify the formula.

[ABC001] =COUNTIFS({Sheet A: Leave_Register : End Date}, >=Date@row, {Sheet A: Leave_Register : Start Date}, <=Date@row, {Sheet A: Leave_Register : Emp Code}, INDEX({Employee Code Index : Code}, 1) = @cell)

[ABC002] =COUNTIFS({Sheet A: Leave_Register : End Date}, >=Date@row, {Sheet A: Leave_Register : Start Date}, <=Date@row, {Sheet A: Leave_Register : Emp Code}, INDEX({Employee Code Index : Code}, 2) = @cell)

As noted above, referencing another sheet with formulas like INDEX({Employee Code Index : Code}, 1), I avoid using cell formulas.

https://app.smartsheet.com/b/publish?EQBCT=b94b4b78d52b41d9ae5ede715a9a0264a (Sheet C: link to the published sheet)

Sagar SV

Hi @jmyzk_cloudsmart_jp

Thanks for the revert.

As you can see below, i'm unable to replicate the formula for other employee codes as it is picking data from the employee code sheet for "ABC001" only. i.e. when i'm implying the same formula in ABC002 column, it is still picking the data of ABC001 only.

Also in the consolidated entry sheet, i'm not getting the desired result. undoubtedly your formulas are correct, but i'm unable to replicate in my sheets. Your help towards this is higly appreciated.

Thanks in advance.

jmyzk_cloudsmart_jp

Hi @Sagar SV

For the Attendance Register -2 Sheet, you must change the row_index from 1 to 2, 3, 4, and so on.

The Consol Engry Sheet 4 error can happen in many ways, so please request a copy-and-share of the workspace that contains the demo solution from the link below. You will get admin access to the workspace.

Please choose "Leave and Timesheet Status Check Proces".

https://app.smartsheet.com/b/form/c06619d504f9418284b4125eec205a54 (Copy and Share Workspace Sevice Request Form)

Please refer to those column info sheets if you prefer to input the formulas yourself.

https://app.smartsheet.com/b/publish?EQBCT=e5cabd4f397b49bfa4f79c8e64e142ef

https://app.smartsheet.com/b/publish?EQBCT=8daf1bb516a9401086083a3ddadd99ea

Sagar SV

Hi @jmyzk_cloudsmart_jp

Thank you so much for sharing the workspace. It helped me a lot by understanding the error i was making while selecting the range. The formulas worked like a charm. Appreciate your support.

Thanks again.