Simple timesheet

[jejones]

I am trying to create a very simple timewheet which is linked to a google sheet and looking for some help.
I have basic fields which include date, day, start time, end time, job number and total hours.

1. How do I calculate the “day” based on the date entered?
2. How do I stop the same times being enetered twice? These are currently captured from a list in the google sheet.
3. The job number is a drop down from a field list on the google sheet. I need to calculate the total time spent on multiple jobs over the same period (in this case, the date).

I need to be abale to create reports from the raw data in the google sheet. I am comfortable with this but struggling a bit with above.

Thanks.

[ThinhDinh]

@jejones

How do I calculate the “day” based on the date entered?

If you mean the “day” as in “date” is 2 May 2024 then “day” being 2, then you can use the math column and have the formula: DAY(D) and point D to the “date” column.

@jejones

How do I stop the same times being enetered twice? These are currently captured from a list in the google sheet.

Is this the exact same time? So no two people can record the same time?

@jejones

The job number is a drop down from a field list on the google sheet. I need to calculate the total time spent on multiple jobs over the same period (in this case, the date).

How would you imagine it being displayed on the front end? When you view a job, you want to summarize how much time is used for it, for each day?

[jejones]

Hi, thanks for responding.
I have sorted the “day” issue buy using the format date solution however, this only updates after the record has been submitted. I need it to update in real time and I apparently, need a custom form for that which I am looking into.

To answer your other questions.
I have a time field which is 15 minute increments taken from a google sheet and placed in a dropdown.
What happens is the person selects a start time and and end time for a specific job.
They can then enter another start / end time for a different job code but these can both be on the same day. I want to prevent them from selecting the same times for both jobs if that makes sense.

I then want to calculate the total hours worked for the day regardless of how many jobs they have submitted times for.

In principle, I thought it would be relatively simple but my knowlege of Glide hasn’t reached that level of maturity.

[Jeff_Hager]

Here are two posts from the same thread that may be useful.

Google sheets takes time to update!

The biggest problem is that you haven’t created the logic to join the date and time into one date value. That’s the part I tried to explain in my previous post. What you have right now is 4 date columns instead of 2. So your query is looking at all 4 as individual dates and times. Let say you make a booking for April 30th from 13:00 to 14:00. Your date only columns will have a date of April 30, but the underlying time is 00:00. Your time only column will have a date of April 26 with a time…

Google sheets takes time to update!

That could very easily be done with all of the logic in Glide and no formulas in the Excel sheet. In addition to the slowness, you are also restricting yourself to only one user using your app at a time by having the logic in your Excel sheet. All you really need is to have a checkout table with two user specific date columns. You can use a date time picker to write dates and times into those user specific columns. In your Cars table, add two single value columns to bring both of those dates…

The rest of it would probably involve some math to calculate a duration between each start and end date time, and then a rollup to add all of durations for the day.