Excel Timesheet: Single User Clock In/Out System

Other Questions Google Sheets
1

I’m working on a timesheet in Excel for one person. I want to track when they start and finish work. The sheet should record the clock-in time in column B and clock-out time in column C, starting from row 2 and moving down with each new entry. The current status (In/Out) should show in cell E2.

Here’s my current code:

function updateCell(col, row, data) {
  let sheet = Excel.getActiveWorksheet()
  sheet.getCell(row, col).setValue(data)
}

function getLastUsedRow() {
  return Excel.getActiveWorksheet().getUsedRange().getLastRow()
}

function clockIn() {
  updateCell('E', 2, 'In')
  let nextRow = getLastUsedRow() + 1
  updateCell('B', nextRow, new Date())
}

function clockOut() {
  updateCell('E', 2, 'Out')
  let nextRow = getLastUsedRow()
  updateCell('C', nextRow, new Date())
}

How can I change this to make it work the way I need? Also, I’d like to calculate the time worked in column D. Any help would be great!

2

Your code is a good start, but there are a few modifications we can make to achieve what you’re looking for. First, let’s adjust the clockIn and clockOut functions to ensure they’re always writing to the correct row. Then, we’ll add a function to calculate the time worked.

Here’s an improved version:

function updateCell(col, row, data) {
  let sheet = Excel.getActiveWorksheet()
  sheet.getCell(row, col).setValue(data)
}

function getLastUsedRow() {
  return Excel.getActiveWorksheet().getUsedRange().getLastRow()
}

function clockIn() {
  updateCell('E', 2, 'In')
  let nextRow = getLastUsedRow() + 1
  updateCell('B', nextRow, new Date())
}

function clockOut() {
  updateCell('E', 2, 'Out')
  let currentRow = getLastUsedRow()
  updateCell('C', currentRow, new Date())
  calculateTimeWorked(currentRow)
}

function calculateTimeWorked(row) {
  let sheet = Excel.getActiveWorksheet()
  let clockIn = sheet.getCell(row, 'B').getValue()
  let clockOut = sheet.getCell(row, 'C').getValue()
  let timeWorked = (clockOut - clockIn) / (1000 * 60 * 60)
  updateCell('D', row, timeWorked.toFixed(2))
}

This should solve your issues and add the time calculation you requested.

3

I’ve been using a similar system for my small business, and I can share some insights. Your approach is solid, but you might want to consider adding a date column to track which day each entry corresponds to. This becomes crucial when you need to generate reports or analyze patterns over time.

For calculating time worked, I’d suggest using a formula in Excel rather than JavaScript. Something like =(C2-B2)*24 in column D will give you the hours worked, and you can format the cell to display it how you prefer.

One thing I learned the hard way: always include a way to edit or delete entries. Mistakes happen, and without an easy correction method, your data can become a mess quickly. Maybe add a simple ‘Edit’ button that allows modification of the last entry?

Lastly, consider implementing a basic data validation to prevent impossible time entries. It’s saved me from quite a few headaches!

4

hey, ur code looks good but i think u can make it beter. maybe add a function to check if someones already clocked in before they try to clock in again? that way u dont get weird overlaps. also for the time worked, u could format it as hours:minutes instead of just hours. just a thought!