Client Formula

You can use any formula as below, just replace all names with your actual column/field name for formula to work.

Basic Operations

Use these formulas in your table columns to perform calculations.

Addition

{price} + {tax}         // Add tax to price, replace price, tax with your actual field
{quantity} + 10         // Add fixed value
{q1} + {q2} + {q3}     // Sum multiple columns

Subtraction

{total} - {discount}    // Subtract discount, replace total, discount with your actual field
100 - {used}           // Subtract from fixed value
{opening} - {closing}   // Calculate difference

Multiplication

{quantity} * {price}    // Calculate total replace quantity, price with your actual field
{hours} * 24           // Multiply by fixed value
{length} * {width}     // Calculate area

Division

{total} / {count}      // Calculate average replace total, count with your actual field
{amount} / 100         // Convert to percentage
{sales} / 12          // Monthly average

Combined Operations

Combine multiple operations for complex calculations:

({price} + {tax}) * {quantity}           // Total with tax replace price, tax & quantity with your actual field
{total} - ({price} * 0.1)                // Apply 10% discount
({revenue} - {cost}) / {revenue} * 100   // Profit margin percentage

Useful Functions

Absolute Values

abs({profit})                // Convert negative to positive replace profit with your actual field
abs({target} - {actual})     // Difference magnitude

Rounding

round({number})              // Round to nearest integer, replace number with your field name
round({number},1)              // Round to single decimal
round({number},2)              // Round to double decimal
round({price} * 1.18)       // Round after calculation, Price is your field name

Conditionals

if({amount} > 1000, {amount} * 0.9, {amount})   // Bulk discount
if({stock} < 0, 0, {stock})                     // Minimum zero

Real Examples

Invoice Calculator

// Item Total
{quantity} * {unitPrice}
 
// Tax Calculation (18%)
{subtotal} * 0.18
 
// Final Amount
{subtotal} + {taxAmount}

Discount Calculator

// Discount Amount
{price} * {discountPercent} / 100
 
// Final Price
{price} - {discountAmount}

Tips

• Select fields from suggested names only when you start typing
• Use parentheses to control calculation order
• Always validate formulas with test data
• Use round() for currency calculations

Common Errors

• Column not found: Check column name spelling and case
• Division by zero: Add validation using if()
• Incorrect parentheses: Verify formula structure has context menu

📅 Date based Formulas Reference Guide

A comprehensive guide for working with dates in worksheet columns

---

🚀 Quick Reference

Operation	Formula	Result
Add Days	dayjs(date).add(7, 'day')	Adds 7 days
Subtract Months	dayjs(date).subtract(2, 'month')	Subtracts 2 months
Date Difference	dayjs(endDate).diff(startDate, 'day')	Days between dates
Format Date	dayjs(date).format('YYYY-MM-DD')	"2024-12-27"

---

📌 Basic Date Operations

⏰ Adding Time

// Adding various time units
 tomorrow = dayjs(date).add(1, 'day')
 nextMonth = dayjs(date).add(1, 'month')
 nextYear = dayjs(date).add(1, 'year')
 later = dayjs(date).add(4, 'hour')
 
// Chaining operations
 futureDate = dayjs(date)
  .add(1, 'month')
  .add(7, 'day')
  .add(12, 'hour')

⏪ Subtracting Time

// Subtracting various time units
 yesterday = dayjs(date).subtract(1, 'day')
 lastMonth = dayjs(date).subtract(1, 'month')
 lastYear = dayjs(date).subtract(1, 'year')

---

🧮 Date Calculations

📏 Date Differences

// Time spans between dates
 daysDiff = dayjs(endDate).diff(startDate, 'day')
 monthsDiff = dayjs(endDate).diff(startDate, 'month')
 yearsDiff = dayjs(endDate).diff(startDate, 'year')
 hoursDiff = dayjs(endDate).diff(startDate, 'hour')

💼 Business Days

// Working with business days
 addBusinessDays = dayjs(date).businessAdd(5)
 subtractBusinessDays = dayjs(date).businessSubtract(3)
 businessDaysDiff = dayjs(endDate).businessDiff(startDate)

---

🎨 Date Formatting

📝 Common Patterns

// Various formatting options
dayjs(date).format('YYYY-MM-DD')         // "2024-12-27"
dayjs(date).format('YYYY-MM-DD HH:mm')   // "2024-12-27 15:30"
dayjs(date).format('MMMM YYYY')          // "December 2024"
dayjs(date).format('dddd')               // "Friday"
dayjs(date).format('DD/MM/YY')           // "27/12/24"

---

✅ Validation & Queries

🔍 Date Comparisons

// Comparing dates
 isBefore = dayjs(date1).isBefore(date2)
 isAfter = dayjs(date1).isAfter(date2)
 isSameOrBefore = dayjs(date1).isSameOrBefore(date2)
 isSameOrAfter = dayjs(date1).isSameOrAfter(date2)

📊 Date Properties

// Extracting date components
 dayOfMonth = dayjs(date).date()     // 1-31
 month = dayjs(date).month()         // 0-11
 year = dayjs(date).year()           // 2024
 dayOfWeek = dayjs(date).day()       // 0-6

---

🌍 Time Zones

// Working with different time zones
 nyTime = dayjs(date).tz('America/New_York')
 localTime = dayjs(date).local()
 utcTime = dayjs(date).utc()

---

🔗 Working with Field References

Using Field References in Formulas

When writing formulas in worksheet columns, use curly brackets {} to reference other fields in your table.

// Calculate days between two date fields
dayjs({endDate}).diff(dayjs({startDate}), 'day')
 
// Add days to a date field
dayjs({dueDate}).add(7, 'day').format('YYYY-MM-DD')
 
// Check if one date is after another
dayjs({completionDate}).isAfter(dayjs({deadlineDate}))

Practical Examples with Field References

// Calculate project duration
// If your fields are named 'projectStart' and 'projectEnd'
dayjs({projectEnd}).diff(dayjs({projectStart}), 'day')
 
// Calculate deadline
// If your field is named 'assignedDate'
dayjs({assignedDate}).add(14, 'day').format('YYYY-MM-DD')
 
// Check if task is overdue
// If your fields are named 'currentStatus' and 'dueDate'
dayjs().isAfter(dayjs({dueDate})) && {currentStatus} !== 'Completed'
 
// Calculate days until deadline
// If your field is named 'deadline'
dayjs({deadline}).diff(dayjs(), 'day')
 
// Format date for display
// If your field is named 'createdAt'
dayjs({createdAt}).format('MMMM D, YYYY')

Complex Field Reference Examples

// Calculate business days between dates
// Fields: 'startDate' and 'endDate'
dayjs({endDate}).businessDiff(dayjs({startDate}))
 
// Check if date is within range
// Fields: 'targetDate', 'rangeStart', 'rangeEnd'
dayjs({targetDate}).isBetween(dayjs({rangeStart}), dayjs({rangeEnd}))
 
// Calculate fiscal quarter
// Field: 'transactionDate'
dayjs({transactionDate}).quarter()
 
// Add business days to a date
// Field: 'submissionDate'
dayjs({submissionDate}).businessAdd(5).format('YYYY-MM-DD')

🎯 Best Practices

Always Validate Inputs

 processDate = (date) => {
  if (!dayjs(date).isValid()) {
    return 'Invalid Date'
  }
  // Process valid date
}

Handle Null Values

 safeDateDiff = (start, end) => {
  if (!start || !end) return null
  return dayjs(end).diff(dayjs(start), 'day')
}

Use Consistent Formats

// Store in ISO, display in local format
 storeDate = dayjs(date).toISOString()
 displayDate = dayjs(storeDate).format('L')

---

🚫 Common Pitfalls to Avoid

Not Checking Validity

// ❌ Wrong
 let age = dayjs().diff(birthDate, 'year')
// ✅ Right
 let age = dayjs(birthDate).isValid() 
  ? dayjs().diff(birthDate, 'year')
  : null

Mutating Date Objects

// ❌ Wrong
let date = dayjs()
date.add(1, 'day')  // This doesn't modify the original
// ✅ Right
let date = dayjs()
date = date.add(1, 'day')  // Reassign the result

Forgetting Timezone Handling

// ❌ Wrong
let date = dayjs(userInput)
// ✅ Right
let date = dayjs(userInput).tz(userTimezone)

---