Working out time worked

[thorinas]

Hi, im trying to design an app with a time sheet. i have the following fields

name,
start time,
lunch taken,
time finish,
formula

what i would like to is for the formula to workout the time between start and finish - lunch.

sorry if this is a simple thing. im not good at coding.

[mikesealey]

Hey @thorinas

This looks like quite a fun little project, and a good use of Budibase. I've mocked up something for you to take a look at, and I'll take you through it step-by-step and share with you my end-product as an app-export so that you can have a hands-on look at it as well.

Firstly, I've started a table that is called "timesheet", and given it a few columns for the scenario, and in brackets I've added their column-type.

• date
• shift_start (date-time (date only))
• lunch_start (date-time)
• lunch_end (date-time)
• shift-end (date-time)
• formula_hours_worked (formula)

I've added a row to this table just by inputting directly into the fields from the data tab. I've set it to be today's date, and my start/finish times and lunch times for today.

So there's a little bit of JavaScript I'm going to show you, but it's nothing complicated, you already know the maths that needs to happen, I'm just going to write it out in code for you.

JavaScript has a Date constructor (read more here) that will take human-readable dates and work with them under the hood in Unix Time which is miliseconds since 1/1/1970. Sounds wild, but actually having everything squashed into one unit means it's easier to do maths with.

What we want to do is subtract the start time from the finish time (eg 17:00 - 09:00 = 08:00 hours worked). In my example I've added in the option to have a start and finish time for the lunch break, which is much the same (13:00 - 12:30 = 00:30) and then we can subtract the lunchbreak from the overall workday.

In the formula_hours_worked column, I've added a JavaScript function that does just that, but making use of the Date constructor, and the Timesheet Fields bindings available on the right:

let lunch = new Date($("lunch_end")) - new Date($("lunch_start"))
let shift = new Date($("shift_end")) - new Date($("shift_start"))

let paid_day = shift - lunch

If you just return paid_day, you'd get the time worked... in milliseconds. We can do a little bit of maths to divide that number by

• 1000 (from miliseconds to seconds)
• 60 (seconds to minutes)
• 60 (minutes to hours)

which looks like this

return paid_day / (1000 * 60 * 60), but strangely returns 7.496944444444445, which is a few miliseconds away from being the value we'd expect - 7.5 hours worked. For this we can wrap the maths that returns the paid_day in brackets, and use .toFixed() which now looks like this:

let lunch = new Date($("lunch_end")) - new Date($("lunch_start"))
let shift = new Date($("shift_end")) - new Date($("shift_start"))

let paid_day = shift - lunch

return (paid_day / (1000 * 60 * 60)).toFixed(2)

Which will return 7.50 hours worked.

I've included an app-export that I've attached here 13995-export-1718988194866.tar.gz. You can have a closer look by importing it to your instance of Budibase (read more here: https://docs.budibase.com/docs/export-and-import-apps)

Hopefully this answers your questions. If you're not already there, head over to our Discord server: https://discord.com/invite/ZepTmGbtfF