Time sheet problems

Hi i’m new here. Trying for days to make my app work. It look so simpel in Excel but really need some help.

I want to use a time picker for begin and end time.
Also a time picker for a lunch
Of course I want a column with ‘total worked hours’ and a column ‘without lunch’.
Than the total worked hours must be rounded on 7:26 to 7:30

But I’m stuck. I’m struggling with time and decimal time.
I don’t know why but when I use (eind - start)*24
he also calculate lunch with it.

Can anyone help me to make the right formula. For calculate time without lunch and to round the total time?

I think before we address the math formula problem, let’s talk about how we get the inputs. This is assuming you will get the inputs from Glide, not an external service.

It must be noted that you won’t be able to catch only the hours & minutes of your start, end & lunch parts using a date/time picker.

I think your start & end columns should be written to by two date/time pickers or an event picker, but it will go with the date alongside the time.

Your lunch column can be written to by a stopwatch, or you can try an alternative by letting the users input the number of minutes they used to have lunch.

OMG you are fast… I need to translate it…

Yes: the input is from Glide with an event picker.

“I think your start & end columns should be written to by two date/time pickers or an event picker, but it will go with the date alongside the time.” OK

Then my preference would be to let the users enter the number of minutes they used to have lunch.

To answer your original question, I formatted all my examples as I said above, timestamps for start & end, numbers representing minutes for lunch time.

The “without lunch” column calculates the total time without lunch, no rounding here.

``````E-S-L/24/60
``````

The “total time” column rounds up to the nearest 30 minute.

``````WL-MOD(WL,1/48)
+CEILING(ROUND(MOD(WL,1/48)*60*24)/30)*30/60/24
``````
3 Likes

I’m going to process it in my Glide. At least now I know where to look and that some things I had come up with are not possible. Thank you for your time and super quick response.

Maybe I’ll eventually have a question with the totals per week. But will try that myself first.

2 Likes

Don’t hesitate asking questions here, we’re always willing to help!

2 Likes

Can I disturb you one more time?
Can I also round it up to the nearest quarter of an hour? 9:22 = 9:15 and 9:23 = 09:30

Just guessing here, but try changing 48 to 96.

Thanks! unfortunately I already tried that.
Quarter of an hour too far.
7:26 should be 7:30, 9:46 should be 9:45

I’m not in front of my computer but I think you should convert your date column to a number then set the corresponding number then put it back to date… or something like that… I seem to have done that before.

1 Like

OK. I’m guessing @ThinhDinh is probably sleeping now. I’m not by a computer right now to try it out either, but I’m sure one of us will get back to you when we have a chance.

1 Like

You are all great! As long as I know it’s possible I’ll keep trying. @jabid Thank you for your tip! Let me try that. But if you’re faster, I’d love to hear it.

Note: My 2nd example top-down is not the same as her since I wanted to trial a case that goes just above 30 mins (31).

I haven’t had time to properly think about rounding to nearest, my formula only rounds up as of now.

And you’re right about 1/96, it’s just that we need to replace 30 by 15 as well.

``````WL-MOD(WL,1/96)
+CEILING(ROUND(MOD(WL,1/96)*60*24)/15)*15/60/24
``````
3 Likes

Yes, this does indeed look better. Thought I had already tried every possible way. But now there is indeed the possibility to round down. 09:46 > 09:45

Apologies for me not thinking about this earlier, CEILING should have been replaced by ROUND and voila.

``````WL-MOD(WL,1/96)
+ROUND(ROUND(MOD(WL,1/96)*60*24)/15)*15/60/24
``````
3 Likes

OMG this is so cool! This works. Thank you so much for your time and thinking along.

2 Likes

My pleasure to help!

1 Like