All Collections
Timesheet
Advanced
Timesheet Column Formula Guidance
Timesheet Column Formula Guidance
StaffAny avatar
Written by StaffAny
Updated over a week ago

Contents of this article are applicable to the following users

Tier: N/A

Platform: Web

Access Level: Owner

The Custom Timesheet Export Format removes the need for you to do error-prone excel transformations every month to run payroll. we allow you to count timesheets based on your custom business rules (role-based rates, outlet-based bonuses, and more)

After which, you can use this while using PayrollAny's pay item or export it out in timesheets to process separately.

This help article covers some of the formulas that you can use in Timesheet Column Format:

  1. Setting up Timesheet Individual custom export formats

    1. Creating a new Timesheet Individual Export Column

    2. Edit the Timesheet Individual Export Column

    3. Deleting the Timesheet Individual Export Column

  2. Formula Element

  3. Formula Format

  4. Use case Example

    1. Based on Days

    2. Based on Hour

    3. Based on Roles

    4. Based on Section

Setting up Timesheet Individual custom export formats

To change your timesheet individual export format:

  1. In StaffAny, go to Settings > Timesheet Export Format > Individual Timesheet
    ​

    Note: The order of the columns shown here from top to bottom maps to the Timesheet Individual Export columns, from left to right.
    ​

  2. Create / Reorder / Rename / Hide the columns (read more below)

  3. Press Save

Creating a new Timesheet Individual Export Column

  1. Click on Create new timesheet column
    ​

  2. Fill in the details for the new custom column

    1. Timesheet Column Name: This will be the default name of the newly created column heading

    2. Column Type: The type of value calculated by the column. Currently, this field is non-editable and defaulted to “number”

    3. Output Unit: The final unit that will be reflected in the Payroll’s pay item in PayrollAny.
      ​

    4. Field formula: Formula for calculating the value of cells in this custom column.

      1. To add a column to the calculation field, you can choose between using a Template or making your formula using variables. While making the formula, click one of the variables that you want to add or you can type the desired column in this format: [Name of the Column]. You can also follow the timesheet formula guidance here.

      2. Basic arithmetic is supported. We also included commonly used arithmetic operators as buttons on the right of the field formula for ease of use.
        ​

    5. Press Check for formula to check whether your formula is correct. if your formula is validated, you can proceed to save the formula.
      ​

    6. The newly created column will be displayed in the Excluded in Export box. You can move your new column to the Include in Export box if you want to include your column in your exported timesheet sheets.
      ​

  3. Press Save to save any changes that you make.
    ​


    ​
    ​

Edit the Timesheet Individual Export Column

To edit a column, there are 2 ways:

  1. Default Column
    ​Note: You can only edit the column name in the default column.
    ​

    1. Click on the name of a column in the Included in Export box.
      ​

    2. After you rename the name, you can still see the original name.
      ​

  2. Custom Column

    1. Click on the edit button.
      ​

    2. You can edit the name, output unit, and formula in this pop-up > check for the formula and any changes > then press Save.
      ​


    ​

Deleting the Timesheet Individual Export Column

Note: You can only delete the custom timesheet column with the bolt icon.

  1. Click on the trash icon to delete
    ​

  2. There will be confirmation modals, click Delete.
    ​

Formula Elements

You can refer to the full list of supported formulas here. Some of the elements that should be included in this formula:

  1. Functions
    We currently support functions that are listed in Formula.JS

  2. Variables
    You can use variables from any column in our timesheet for this formula.

Formula Format

To ensure that the formula format is correct and working fine, please note the following format requirements:

  • All functions (e.g., IF, MIN, TIMEVALUE) must be in capital letters.

  • Equals signs should be in double (==)."

  • Use double quotation marks (“...”) to differentiate text.

  • Use square brackets ( [ ] ) to denote variables, which are data from your timesheets.

For Example:

Use Case Example

Based on Days

If you have a calculation involving a certain range of or a couple of days, you may use some of these existing formulations:

Days Serial Number:

Days

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Serial Number

1

2

3

4

5

6

7

  • Case 1: Total paid hours for certain days

    If you have a different calculation for a certain day, for example, there is a different pay rate for your staff that worked on Tuesday (3) and Friday (6) for your staff.
    ​

    Formula:

    IF(OR(WEEKDAY([Date]), 2) == 3, WEEKDAY([Date], 2) == 6), [Total Paid Hours], 0)

    Used functions:

    • IF - function used to perform a conditional test and return one value if the test evaluates to true
      Syntax: IF(Conditions, value if true, value if false)

    • OR - function to test more than one condition, usually paired with another logical function such as IF, AND, & NOT
      Syntax: OR(Conditions_1, Conditions_2 ,...)

    • WEEKDAY - Function that returns the day of the week as a number (ranging from 1 to 7), given a date. It allows you to determine the day of the week for a specific date.
      Syntax: WEEKDAY(serial_number, [return_type])

  • Case 2: Public holiday rate for weekend

    If you have a different calculation for your staff that work on public holidays (PH) that falls on weekends, you can use this formula.

    Formula:

    IF(WEEKDAY([Date], 2) > 5, [Event Hours], 0)

    Notes: please set up “Event” under special dates. Learn more about how to set up special dates

    Used functions:

    • IF - function used to perform a conditional test and return one value if the test evaluates to true
      Syntax: IF(Conditions, value if true, value if false)

    • WEEKDAY - Function that returns the day of the week as a number (ranging from 1 to 7), given a date. It allows you to determine the day of the week for a specific date.
      Syntax: WEEKDAY(serial_number, [return_type])
      ​
      ​

  • Case 3: Public holiday rate for weekday

    If you have a different calculation for your staff that work on public holidays (PH) that fall on weekdays, you can use this formula.

    Formula:

    IF(WEEKDAY([Date], 2) < 6, [Event Hours], 0)

    Notes: please set up “Event” under special dates. Learn more about how to set up special dates

    Used functions:

    • IF - function used to perform a conditional test and return one value if the test evaluates to true
      Syntax: IF(Conditions, value if true, value if false)

    • WEEKDAY - Function that returns the day of the week as a number (ranging from 1 to 7), given a date. It allows you to determine the day of the week for a specific date.
      Syntax: WEEKDAY(serial_number, [return_type])

Based on Hour

If you pay your staff based on their clock-in or clock-out times, or hours worked before or after, you may use some of these existing formulations:

  • Case 1: Total hours worked before 9 PM

    If you have a different calculation if your staff worked before a certain time, you may use this formula.

    Formula:

    MIN((TIMEVALUE(CONCATENATE([Date], ' ', '9:00 PM')) - TIMEVALUE(CONCATENATE([Date], ' ', [Time In]))) * 24, [Total Paid Hours])

    Used functions:

    • MIN - function used to find the smallest value in the references
      Syntax: MIN(Number_1, Number_2,...)

    • TIMEVALUE - function converts a time data stored as text into a decimal number representing the time in Excel’s internal numeric format.
      Syntax: TIMEVALUE(Time_text)

    • CONCATENATE - a function used to join together multiple strings or cell values into a single string. It's commonly used when combining text values from different cells or adding additional text strings to cell values.
      Syntax: CONCATENATE(text_1,text 2,...)
      ​

  • Case 2: Total hours worked after 9 PM

    If you have a different calculation if your staff worked after a certain time, you may use this formula

    Formula:

    [Total Paid Hours] - MIN((TIMEVALUE(CONCATENATE([Date], ' ', '9:00 PM')) - TIMEVALUE(CONCATENATE([Date], ' ', [Time In]))) * 24, [Total Paid Hours])

    Used functions:

    • MIN - function used to find the smallest value in the references
      Syntax: MIN(Number_1, NUmber_2,...)

    • TIMEVALUE - function converts a time data stored as text into a decimal number representing the time in Excel’s internal numeric format.
      Syntax: TIMEVALUE(Time_text)

    • CONCATENATE - a function used to join together multiple strings or cell values into a single string. It's commonly used when combining text values from different cells or adding additional text strings to cell values.
      Syntax: CONCATENATE(text_1,text 2,...)
      ​

Based on Roles

If you have different rates for different roles, like for chefs and cashiers, you can create two separate formulas:

The formula for Cashier :

IF([Role]==”Cashier”,[Total Paid Hours],0)

The formula for Chef:

IF([Role]==”Chef”,[Total Paid Hours],0)

Used functions:

  • IF - function used to perform a conditional test and return one value if the test evaluates to true
    Syntax: IF(Conditions, value if true, value if false)

Based on Section

If you have different rates for different sections. For example, you have different rates for Changi Airport since the sections are busier than the others. You can use this formula:

Formulas:

IF([Section]=="Changi Airport",[Total Paid Hours],0)

Used functions:

  • IF - function used to perform a conditional test and return one value if the test evaluates to true
    Syntax: IF(Conditions, value if true, value if false)

Did this answer your question?