Formula for calculation

Sagar SV · 09/20/24

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 · 09/24/24

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 · 09/24/24

Happy to help!😁 Glad to hear you solved the range issue. @Sagar SV

jmyzk_cloudsmart_jp · 09/27/24

Hi @Sagar SV

You can use the WEEKDAY function to determine the days of the week.

[Date 3] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 3) + "", IF(OR(WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 3)) = 1, WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 3)) = 7), "Week End", 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}, 3)) > 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}, 3)))), "Complete", "Pending"))))

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

Sagar SV · 09/27/24

Hi @jmyzk_cloudsmart_jp,

Thank you so much for the support. Formula worked smoothly.

jmyzk_cloudsmart_jp · 11/22/24

Hi @Sagar SV

You can add an IF clause like this before the weekend check;

IF(HAS({us-federal-holidays : date}, INDEX({Sheet B: Attendance_Register : Date}, 1)), "Holiday"

The {us-federal-holidays : date} range refers to the date column range of the following example holiday sheet.

Then, use the HAS formula to check a date is in the range.

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

As you can see in the above image, I added Test Dates 1 and 7, and the formulas are working.

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

I added the following IF clause to prevent errors in rows Employee ID is blank.

IF(ISBLANK([Employee ID]@row), "",

[Date 1] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 1) + "", IF(ISBLANK([Employee ID]@row), "", IF(HAS({us-federal-holidays : date}, INDEX({Sheet B: Attendance_Register : Date}, 1)), "Holiday", IF(OR(WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 1)) = 1, WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 1)) = 7), "Weekend", 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] …..
[Date 7] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 7) + "", IF(ISBLANK([Employee ID]@row), "", IF(HAS({us-federal-holidays : date}, INDEX({Sheet B: Attendance_Register : Date}, 7)), "Holiday", IF(OR(WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 7)) = 1, WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 7)) = 7), "Weekend", 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}, 7)) > 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}, 7)))), "Complete", "Pending"))))))

To save you time, I added the above sheet to the shared workspace and updated the formulas for Dates 1 to 28. Please check the workspace.

jmyzk_cloudsmart_jp · 09/22/24

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]

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.
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.
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 · 09/23/24

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 · 09/24/24

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 · 09/24/24

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.

jmyzk_cloudsmart_jp · 09/24/24

Happy to help!😁 Glad to hear you solved the range issue. @Sagar SV

Sagar SV · 09/26/24

Hi @jmyzk_cloudsmart_jp

Is there any way, where we can include weekend too in the timesheet consol entry? i.e. in the below screenshot, if it can show on 03/08/24 as weekend (saturday) & 04/08/24 (sunday), rest all the dates with the given criteria as "pending", "on leave" or "completed".

Thanks in advance.

jmyzk_cloudsmart_jp · 09/27/24

Hi @Sagar SV

You can use the WEEKDAY function to determine the days of the week.

[Date 3] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 3) + "", IF(OR(WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 3)) = 1, WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 3)) = 7), "Week End", 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}, 3)) > 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}, 3)))), "Complete", "Pending"))))

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

Sagar SV · 09/27/24

Hi @jmyzk_cloudsmart_jp,

Thank you so much for the support. Formula worked smoothly.

Sagar SV · 11/21/24

Hi @ jmyzk_cloudsmart_jp,

For the given formula, i have an list of public holidays in one of the sheet. With the current formula support that you have provided, could you pls help with the addition of the holiday list along with weekend and on leave data?

Thanks in advance for the support.

jmyzk_cloudsmart_jp · 11/22/24

Hi @Sagar SV

You can add an IF clause like this before the weekend check;

IF(HAS({us-federal-holidays : date}, INDEX({Sheet B: Attendance_Register : Date}, 1)), "Holiday"

The {us-federal-holidays : date} range refers to the date column range of the following example holiday sheet.

Then, use the HAS formula to check a date is in the range.

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

As you can see in the above image, I added Test Dates 1 and 7, and the formulas are working.

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

I added the following IF clause to prevent errors in rows Employee ID is blank.

IF(ISBLANK([Employee ID]@row), "",

[Date 1] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 1) + "", IF(ISBLANK([Employee ID]@row), "", IF(HAS({us-federal-holidays : date}, INDEX({Sheet B: Attendance_Register : Date}, 1)), "Holiday", IF(OR(WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 1)) = 1, WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 1)) = 7), "Weekend", 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] …..
[Date 7] =IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 7) + "", IF(ISBLANK([Employee ID]@row), "", IF(HAS({us-federal-holidays : date}, INDEX({Sheet B: Attendance_Register : Date}, 7)), "Holiday", IF(OR(WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 7)) = 1, WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 7)) = 7), "Weekend", 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}, 7)) > 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}, 7)))), "Complete", "Pending"))))))

To save you time, I added the above sheet to the shared workspace and updated the formulas for Dates 1 to 28. Please check the workspace.

Sagar SV · 11/22/24

Hi @jmyzk_cloudsmart_jp,

Thank you so much for the help. Formula worked smoothly and many thanks for creating the same into the shared workspace. Thanks a lot..

jmyzk_cloudsmart_jp · 11/22/24

Happy to help!😁

Sagar SV · 11/28/24

Hi @jmyzk_cloudsmart_jp,

Thanks for showing the support. Again I stuck in one formula where in the console entry sheet, when i'm using SUMIFS formula, it doesn't show any value.

Formula for calculation

Best Answers

Answers

Formula Breakdown: [Date 1]

Help Article Resources

Categories