Formula for calculation

Sagar SV
Sagar SV ✭
in Formulas and Functions

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

image.png

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")

image.png

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")

image.png

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

image.png

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.

Tags:
· Share on FacebookShare on Twitter

Best Answers

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 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)

    image.png

    (**)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)

    image.png

    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)

    image.png

    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)

    image.png

    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)

    image.png

    · Share on FacebookShare on Twitter
  • Sagar SV
    Sagar SV ✭
    edited 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.

    image.png

    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.

    image.png

    Thanks in advance.

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    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)

    image.png

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

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

    image.png

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

    image.png

    · Share on FacebookShare on Twitter
  • Sagar SV
    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.

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

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

    · Share on FacebookShare on Twitter
  • Sagar SV
    Sagar SV ✭

    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".

    image.png

    Thanks in advance.

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    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

    image.png

    · Share on FacebookShare on Twitter
  • Sagar SV
    Sagar SV ✭
    Answer ✓

    Hi @jmyzk_cloudsmart_jp,

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

    · Share on FacebookShare on Twitter
  • Sagar SV
    Sagar SV ✭
    edited 8:37AM

    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!